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?

Actually I tried multiple things, like ordering the columns with different combinations, joining this same result table with itself, getting child count and then trying to use it and many more but nothing gave even close result and that's why I did not post what all I tried :( But believe me I tried a lot first before posting question. — Pawan Nogariya, Dec 05 '16 at 12:13
we did sth like `CAST(0 AS VARBINARY(MAX)) AS Level_sort` for the first group and unioned it with the id of the the item and added their varbinary into the levelsort. `Level_sort + CAST(i.ID AS VARBINARY(MAX)) AS LEVEL_sort` In the end you can order it by Level_sort. But be aware that varbinary(max) can be really long and also slow down your engine. — Nebi, Dec 05 '16 at 12:23

Eralper · Answer 1 · 2016-12-05T13:29:59.280

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

As in my comment to Nebi above, The convert row_number() to char does not provide a proper sequence. just for fun, try Select Seq from (Select Seq=cast(1 as char(10))Union All Select Seq=cast(2 as char(10)) Union All Select Seq=cast(10 as char(10)) ) A Order by 1 — John Cappelletti, Dec 05 '16 at 13:09
Seriously, take a moment an see what happens when Row_Number() exceeds 10 items — John Cappelletti, Dec 05 '16 at 13:37
This means the number of rows under a note to be 10000000000 . Too much to consider — Eralper, Dec 05 '16 at 13:39
The sort is used for providing treeview, so it will display the users under the same branch one an after. The order among siblings is not targeted here — Eralper, Dec 05 '16 at 13:41

John Cappelletti · Answer 2 · 2016-12-05T13:43:56.737

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)))

why are you adding `1000` to the `Row_Number`? What happens if the Row_Number goes beyond 1000 rows? But either way isn't that the same approach like @Eralper did? Using Row_Number as sorting criteria? — Nebi, Dec 05 '16 at 12:46
@Nebi - consistany in sequencing. You would need more than 9,999 peers to be a concern — John Cappelletti, Dec 05 '16 at 12:52
@Nebi just for fun, try Select Seq from (Select Seq=cast(1 as char(10))Union All Select Seq=cast(2 as char(10)) Union All Select Seq=cast(10 as char(10)) ) A Order by 1 — John Cappelletti, Dec 05 '16 at 12:56
allright thanks for pointing out. That is why we used varbinary. Like that: SELECT num, Seq FROM ( SELECT 1 AS num, CAST(1 AS Varbinary(10))AS Seq UNION ALL SELECT 2 AS num, CAST(2 AS Varbinary(10)) AS Seq UNION ALL SELECT 10 AS num , CAST(10 AS Varbinary(10))AS Seq ) A ORDER BY Seq — Nebi, Dec 05 '16 at 13:53
but my concerns begin when I have more than 8999 peers, because of the additional 1000, or am i wrong? It is probably not concerning in most cases, but it is worth mentioning. — Nebi, Dec 05 '16 at 14:00
@Nebi You are 100% correct in pointing out that varbinary can be a drag on performance. I deal with large hierarchies (175K) and there was a notable performance — John Cappelletti, Dec 05 '16 at 14:00
@Nebi the 9999 is for smaller / portions / ad-hoc hierarchies. I use a another process for building LARGE hierarchies. 200K nodes in 7 seconds — John Cappelletti, Dec 05 '16 at 14:03

Recursive query final result CTE

2 Answers2