Recursive query final result CTE

Question

I have written a CTE query and this is the result I am getting

Item        Parent    Level  Group
--------------------------------------
CRSM/002    NULL        0   CRSM/002
7160/002    CRSM/002    1   CRSM/002
7823/085    CRSM/002    1   CRSM/002
7864/038    CRSM/002    1   CRSM/002
A543/033    CRSM/002    1   CRSM/002
7460/530    CRSM/002    1   CRSM/002
7280/007    7160/002    2   CRSM/002
7009/130    7160/002    2   CRSM/002
7567/001    7160/002    2   CRSM/002
7009/126    7160/002    2   CRSM/002
7280/003    7160/002    2   CRSM/002
7280/008    7160/002    2   CRSM/002
7280/005    7160/002    2   CRSM/002
7574/004    7160/002    2   CRSM/002
7280/004    7160/002    2   CRSM/002
7280/006    7160/002    2   CRSM/002
7454/224    7280/006    3   CRSM/002
7093/633    7280/006    3   CRSM/002
7202/010    7280/006    3   CRSM/002
7202/013    7280/004    3   CRSM/002
7454/253    7280/004    3   CRSM/002
7093/553    7280/005    3   CRSM/002
8865/957    7280/005    3   CRSM/002
7202/012    7280/005    3   CRSM/002
7093/696    7280/008    3   CRSM/002
7202/011    7280/008    3   CRSM/002
7454/294    7280/008    3   CRSM/002
7202/009    7280/003    3   CRSM/002
7454/201    7280/003    3   CRSM/002
7656/002    7009/126    3   CRSM/002
A556/075    7009/126    3   CRSM/002
7574/002    7009/126    3   CRSM/002
A902/027    7009/126    3   CRSM/002
7093/418    7280/007    3   CRSM/002
7454/245    7280/007    3   CRSM/002
7202/008    7280/007    3   CRSM/002

It is returning proper data with all the levels of hierarchy, parent child relation and root level parent grouping.

The data is coming correct but I want data to be arranged in tree structure, meaning currently the arrangement is like this level 0 row first and then all the element with level 1 row and so.. on..

What I want the arrangement to be is, 0 level first and then 1st level 1st child and if that child has children then those children which will be in second level and etc. So it will become the actual tree structure. I don't know if I was able to explain my problem properly or not. I have tried ordering and other combinations but couldn't get success.

Is there any way?

Answer 1

Just need to add a sequence during the build.

I'm using ITEM in the Order By, but you could use the Item Description or another presentation sequence key if avail.

See comments in DECLARE @Top and @Nest. See what happens

Declare @Table table (Item varchar(25), Parent varchar(25))
Insert into @Table values ('CRSM/002',NULL),('7160/002','CRSM/002'),('7823/085','CRSM/002'),('7864/038','CRSM/002'),('A543/033','CRSM/002'),('7460/530','CRSM/002'),('7280/007','7160/002'),('7009/130','7160/002'),('7567/001','7160/002'),('7009/126','7160/002'),('7280/003','7160/002'),('7280/008','7160/002'),('7280/005','7160/002'),('7574/004','7160/002'),('7280/004','7160/002'),('7280/006','7160/002'),('7454/224','7280/006'),('7093/633','7280/006'),('7202/010','7280/006'),('7202/013','7280/004'),('7454/253','7280/004'),('7093/553','7280/005'),('8865/957','7280/005'),('7202/012','7280/005'),('7093/696','7280/008'),('7202/011','7280/008'),('7454/294','7280/008'),('7202/009','7280/003'),('7454/201','7280/003'),('7656/002','7009/126'),('A556/075','7009/126'),('7574/002','7009/126'),('A902/027','7009/126'),('7093/418','7280/007'),('7454/245','7280/007'),('7202/008','7280/007')

Declare @Top  varchar(25) = null     --<<  Sets top of Hier Try '77009/126'
Declare @Nest varchar(25) ='|-----'  --<<  Optional: Added for readability

;with cteHB as (
      Select Seq  = cast(1000+Row_Number() over (Order by Item) as varchar(500))
            ,Item
            ,Parent
            ,Lvl=1
      From   @Table 
      Where  IsNull(@Top,'') = case when @Top is null then isnull(Parent,'') else Item end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',1000+Row_Number() over (Order by r.Item)) as varchar(500))
            ,r.Item
            ,r.Parent
            ,p.Lvl+1
      From   @Table r 
      Join   cteHB p on r.Parent = p.Item)
     ,cteR1 as (Select Seq,Item,R1=Row_Number() over (Order By Seq) From cteHB)
     ,cteR2 as (Select A.Seq,A.Item,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.Item )
Select B.R1  
      ,C.R2
      ,A.Item
      ,A.Parent
      ,A.Lvl
      ,Title = Replicate(@Nest,A.Lvl-1) + A.Item
 From cteHB A
 Join cteR1 B on A.Item=B.Item
 Join cteR2 C on A.Item=C.Item
 Order By B.R1,A.Seq

Returns

EDIT - I should Add

The range keys are optional, just remove cteR1 and cteR2 if not needed. I use these keys of non-recursive aggregation.

EDIT - Demonstration of char(10)

Using the convert( char(10),Row_Number() over (Order by r.Item)))

Answer 2

Please check following post Parent/Child hierarchy like tree view

As you can see from the codes of SQL recursive CTE query, I used depth (order in hierarchy) and sort columns. Sort column provides the treeview actually. To make the visualization more clear I added a 3 space characters while going down in the hierarchy

sample data

declare @pc table(ID int, PARENT_ID int, [NAME] varchar(80));

insert into @pc
select 1,NULL,'Bill' union all
select 2,1,'Jane' union all
select 3,1,'Steve' union all
select 4,2,'Ben' union all
select 5,3,'Andrew' union all
select 6,NULL,'Tom' union all
select 7,8,'Dick' union all
select 8,6,'Harry' union all
select 9,3,'Stu' union all
select 10,7,'Joe';


; with r as (
    -- anchor part
      select ID, 
          PARENT_ID, 
          [NAME], 
          depth = 0 , 
          sort = convert(varchar(max), convert( char(10), ROW_NUMBER() over (order by ID)))
      from @pc
      where PARENT_ID is null

      union all

    -- recursive part
      select 
        pc.ID, 
        pc.PARENT_ID, 
        pc.[NAME], 
        depth = r.depth + 1, 
        sort = r.sort + convert( char(10), ROW_NUMBER() over (order by pc.ID))
      from r
      inner join @pc pc on r.ID=pc.PARENT_ID
)
select 
    space(depth*3) + name
from r
order by sort

Output is as follows

About sorting of data