TSQL recursive CTE order

Question

I am having trouble figuring out how I can use recursive CTEs to order my results recursively. Here is what I mean (this is a simplified dataset):

I have this as input:

declare @sections table (id int, parent int);

insert into @sections values (1, 1);
insert into @sections values (2, 2);
insert into @sections values (3, 2);
insert into @sections values (4, 2);
insert into @sections values (5, 4);
insert into @sections values (6, 1);
insert into @sections values (7, 6);
insert into @sections values (8, 6);
insert into @sections values (9, 6);
insert into @sections values (10, 9);

-- hierarchical view
--1
--  6
--      7
--      8
--          10
--      9
--2
--  3
--  4
--      5

And I want this as output EDIT: The order of the rows is the important part here

--  id  parent  depth
--  1   1       0
--  6   1       1
--  7   6       2
--  8   6       2
-- 10   8       3
--  9   6       2
--  2   2       0

This is the best I can do:

with section_cte as
(
    select id, parent, 0 'depth' from @sections where id = parent
    union all
    select cte.id, cte.parent, depth + 1 
    from @sections s join section_cte cte on s.parent = cte.id where s.id <> s.parent
)
select *from section_cte

Can anyone please help me tweak this query to get what I need?

Thanks!

Answer 1

During the recursive build, it is a small matter to add a sequence. In the example below, the order is driven by the alphabetical order of the title, but you can use any other available key/sequence.

Declare @Table table (ID int,Pt int,Title varchar(50))
Insert into @Table values (0,null,'Tags'),(1,0,'Transportation'),(2,1,'Boats'),(3,1,'Cars'),(4,1,'Planes'),(5,1,'Trains'),(6,0,'Technology'),(7,6,'FTP'),(8,6,'HTTP'),(9,0,'Finance'),(10,9,'FTP'),(11,9,'401K'),(12,2,'Sail'),(13,2,'Powered'),(14,6,'Internet'),(15,6,'Database'),(16,15,'SQL Server'),(17,15,'MySQL'),(18,15,'MS Access')

Declare @Top  int = null          --<<  Sets top of Hier Try 9
Declare @Nest varchar(25) ='   '  --<<  Optional: Added for readability

;with cteHB (Seq,ID,Pt,Lvl,Title) as (
    Select  Seq  = cast(1000+Row_Number() over (Order by Title) as varchar(500))
           ,ID
           ,Pt
           ,Lvl=1
           ,Title 
     From   @Table 
     Where  IsNull(@Top,-1) = case when @Top is null then isnull(Pt,-1) else ID end
     Union  All
     Select Seq  = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.Title)) as varchar(500))
           ,cteCD.ID
           ,cteCD.Pt,cteHB.Lvl+1
           ,cteCD.Title 
     From   @Table cteCD 
     Join   cteHB on cteCD.Pt = cteHB.ID)
     ,cteR1 as (Select Seq,ID,R1=Row_Number() over (Order By Seq) From cteHB)
     ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select Hier='HierName'
      ,B.R1  
      ,C.R2
      ,A.ID
      ,A.Pt
      ,A.Lvl
      ,Title = Replicate(@Nest,A.Lvl) + A.Title
      --,A.Seq               --<< Normally Excluded, but you can see how the sequence is built
 From cteHB A
 Join cteR1 B on A.ID=B.ID
 Join cteR2 C on A.ID=C.ID
 Order By A.Seq              --<< Use R1 if Range Keys are used

Returns

Hier        R1  R2  ID  Pt      Lvl    Title
HierName    1   19  0   NULL    1      Tags
HierName    2   4   9   0       2         Finance
HierName    3   3   11  9       3            401K
HierName    4   4   10  9       3            FTP
HierName    5   12  6   0       2         Technology
HierName    6   9   15  6       3            Database
HierName    7   7   18  15      4               MS Access
HierName    8   8   17  15      4               MySQL
HierName    9   9   16  15      4               SQL Server
HierName    10  10  7   6       3            FTP
HierName    11  11  8   6       3            HTTP
HierName    12  12  14  6       3            Internet
HierName    13  19  1   0       2         Transportation
HierName    14  16  2   1       3            Boats
HierName    15  15  13  2       4               Powered
HierName    16  16  12  2       4               Sail
HierName    17  17  3   1       3            Cars
HierName    18  18  4   1       3            Planes
HierName    19  19  5   1       3            Trains

Now, you may have noticed R1 and R2. These are my range keys, and are often used to aggregate data without recursion. If you don't need or want these, just remove the cteR1 and cteR2 (and the corresponding references in the final SELECT).

EDIT

You also have the option of selecting a portion of the hierarchy (i.e. Technology and its children).

Answer 2

The trick you need is to create a sortstring which match your business rule. Something like this.

WITH CTE AS (
SELECT
    id, parent, 0 AS depth
    --For MS SQL Server 2012+
    ,cast(format(id,'0000') as varchar(max)) sort
    --For previous versions
    ,cast(stuff('0000',5-len(cast(id as varchar)),len(cast(id as varchar)),id) as varchar(max)) sort1
FROM @sections
WHERE id=parent
UNION ALL
SELECT s.id, s.parent, c.depth + 1
    ,sort + cast(format(s.id,'0000') as varchar(max)) sort
    ,sort + cast(stuff('0000',5-len(cast(s.id as varchar)),len(cast(s.id as varchar)),s.id) as varchar(max)) sort1
FROM @sections s
    inner join CTE c ON s.parent=c.id AND s.id <> s.parent
)
SELECT *
FROM CTE
order by sort --or by sort1 depending on version

Answer 3

You missed the part where you need to identify the depth is from the cte

WITH CTE AS (
SELECT
    id
    , parent
    , 0 AS depth
FROM
    @sections
WHERE
    id=parent

UNION ALL

SELECT
    s.id
    , s.parent
    , c.depth + 1
FROM
    @sections s
    JOIN CTE c ON s.parent=c.id AND s.id <> s.parent
)
SELECT *
FROM CTE;