How to print out the hierarchical values of a table in a specific layout

ricardos · October 27, 2022, 2:34pm

Hi have a book subdivided in chapters, section, and articles. The content of the book is in a table database like the following

book table

chapter	section	article
Chapter 1	Section 1	Article 1
Chapter 1	Section 1	Article 2
Chapter 1	Section 2	Article 3
Chapter 2	Section 1	Article 4
Chapter 2	Section 2	Article 5
...	...	...

I want to print the book so that I have

Chapter 1:
   Section 1:
     Article 1
     Article 2
   Section 2:
     Article 3
Chapter 2:
   Section 1:
     Article 4
   Section 2:
     Article 5

How can I do this?

I've tried

WITH cte AS (
    SELECT chapter, article AS p1, 1 AS p2 FROM test
    UNION ALL
    SELECT section, article, 2 FROM test
    UNION ALL
    SELECT article, article, 3 FROM test
)

SELECT chapter AS val
FROM cte
ORDER BY p1, p2;

but this results in

Chapter 1:
   Section 1:
     Article 1
Chapter 1:
   Section 1:
     Article 2
Chapter 1:
   Section 2:
     Article 3
Chapter 2:
   Section 1:
     Article 4
Chapter 2:
   Section 2:
     Article 5

which has several rows repeated that I don't want

regards

harishgg1 · October 29, 2022, 7:30am

hi hope this helps

create data script

create table #book (chapter varchar(20),section varchar(20), article varchar(20))
insert into #book select 'Chapter 1','Section 1','Article 1'
insert into #book select 'Chapter 1','Section 1','Article 2'
insert into #book select 'Chapter 1','Section 2','Article 3'
insert into #book select 'Chapter 2','Section 1','Article 4'
insert into #book select 'Chapter 2','Section 2','Article 5'

select * from #book

; with cte as 
(
     select 
	    ROW_NUMBER() over(   order by chapter , section , article ) as rn , 
        * 
 	from 
	   #book
) 
,
  cte_union as 
(
    select rn,chapter from cte 
      union all 
    select rn,section from cte 
      union all 
    select rn,article from cte 
)
select * from cte_union 
order by rn 
         , case when chapter like '%Chapter%' then 1  
		        when chapter like '%Section%' then 2 
                when chapter like '%Article%' then 3 end