Dynamic T-SQL query for hierarchical data

Question

I am trying to do a dynamic query to go through child - parents table and I have been able to go through top and second level of hierarchical query:

Data:

create table temp 
(
     Pos int
    ,Child nvarchar(18)
    ,Parent nvarchar(18)
    ,Test int
);

insert into temp (Pos, Child, Parent, Test)
values
(1, 'A', NULL, 1),
(2, 'J', NULL, 10),
(3, 'P', NULL, 16),
(4, 'Y', NULL, 25),
(1, 'B', 'A', 2),
(2, 'E', 'A', 5),
(1, 'C', 'B', 3),
(2, 'D', 'B', 4),
(1, 'F', 'E', 6),
(2, 'G', 'E', 7),
(1, 'H', 'G', 8),
(2, 'I', 'G', 9),
(1, 'K', 'J', 11),
(2, 'L', 'J', 12),
(3, 'M', 'J', 13),
(1, 'N', 'M', 14),
(2, 'O', 'M', 15),
(5, 'Z', NULL, 26),
(1, 'Q', 'P', 17),
(2, 'S', 'P', 19),
(3, 'T', 'P', 20),
(4, 'X', 'P', 24),
(1, 'R', 'Q', 18),
(1, 'U', 'T', 21),
(2, 'V', 'T', 22),
(3, 'W', 'T', 23)

Column Test is only to see at the end if the data are correctly ordered

My code so far:

declare @sql nvarchar(max);
declare @tlp nvarchar(max); --top level parents
declare @i nvarchar(4);
declare @j nvarchar(4);
declare @l nvarchar(4); --level

set @tlp = ';with tlp as (
       select ROW_NUMBER() over (order by Pos) as j, * from temp where Parent IS NULL
       )';
set @i = 1;
set @j = (select COUNT(*) as j from temp where Parent IS NULL);
set @sql = @tlp;

while @i < @j
    begin
        set @l = 1;
        set @sql += '
            select ' + @l + ' as Level, * from tlp where j = ' + @i
        
        set @l = @l + 1
        set @sql += '
            union all
            select ' + @l + ' as Level, ROW_NUMBER() over (order by Pos), * from temp where Parent = (select Child from tlp where j = ' + @i + ')'
        set @i = @i + 1
        if @i < @j set @sql += '
            union all'
end;

exec(@sql);

Output:

level   j   Pos Child   Parent  Test
1       1   1   A       NULL    1
2       1   1   B       A       2
2       2   2   E       A       5
1       2   2   J       NULL    10
2       1   1   K       J       11
2       2   2   L       J       12
2       3   3   M       J       13
1       3   3   P       NULL    16
2       1   1   Q       P       17
2       2   2   S       P       19
2       3   3   T       P       20
2       4   4   X       P       24
1       4   4   Y       NULL    25

How can I excend the query to dynamicaly go through all the childs? This is the desired output:

Level   j   Pos Child   Parent  Test
1       1   1   A       NULL    1
2       1   1   B       A       2
3       1   1   C       B       3
3       2   2   D       B       4
2       2   2   E       A       5
3       1   1   F       E       6
3       2   2   G       E       7
4       1   1   H       G       8
4       2   2   I       G       9
1       2   2   J       NULL    10
2       1   1   K       J       11
2       2   2   L       J       12
2       3   3   M       J       13
3       1   1   N       M       14
3       2   2   O       M       15
1       3   3   P       NULL    16
2       1   1   Q       P       17
3       1   1   R       Q       18
2       2   2   S       P       19
2       3   3   T       P       20
3       1   1   U       T       21
3       2   2   V       T       22
3       3   3   W       T       23
3       4   4   X       P       24
1       4   4   Y       NULL    25
1       5   5   Z       NULL    26

Here is a visual interpretation what I was trying to achieve:

Answer 1

I don't see the need for dynamic SQL at all. You have hierarchical data that you want to traverse depth-first: in SQL, this is typically done with a recursive query.

To manage the ordering of the rows, you can keep track of the path to each node.

Consider:

with cte as (
    select t.*, 1 lvl, cast(child as nvarchar(max)) path 
    from temp t 
    where parent is null
    union all
    select t.*, c.lvl + 1, c.path + '/' + cast(t.child as nvarchar(max))
    from cte c
    inner join temp t on t.parent = c.child
)
select * from cte order by path

Demo on DB Fiddle:

Pos | Child | Parent | Test | lvl | path   
--: | :---- | :----- | ---: | --: | :------
  1 | A     | null   |    1 |   1 | A      
  1 | B     | A      |    2 |   2 | A/B    
  1 | C     | B      |    3 |   3 | A/B/C  
  2 | D     | B      |    4 |   3 | A/B/D  
  2 | E     | A      |    5 |   2 | A/E    
  1 | F     | E      |    6 |   3 | A/E/F  
  2 | G     | E      |    7 |   3 | A/E/G  
  1 | H     | G      |    8 |   4 | A/E/G/H
  2 | I     | G      |    9 |   4 | A/E/G/I
  2 | J     | null   |   10 |   1 | J      
  1 | K     | J      |   11 |   2 | J/K    
  2 | L     | J      |   12 |   2 | J/L    
  3 | M     | J      |   13 |   2 | J/M    
  1 | N     | M      |   14 |   3 | J/M/N  
  2 | O     | M      |   15 |   3 | J/M/O  
  3 | P     | null   |   16 |   1 | P      
  1 | Q     | P      |   17 |   2 | P/Q    
  1 | R     | Q      |   18 |   3 | P/Q/R  
  2 | S     | P      |   19 |   2 | P/S    
  3 | T     | P      |   20 |   2 | P/T    
  1 | U     | T      |   21 |   3 | P/T/U  
  2 | V     | T      |   22 |   3 | P/T/V  
  3 | W     | T      |   23 |   3 | P/T/W  
  4 | X     | P      |   24 |   2 | P/X    
  4 | Y     | null   |   25 |   1 | Y      
  5 | Z     | null   |   26 |   1 | Z      

If a path may have more than 100 nodes, then you need to add option(maxrecursion 0) at the end of the query, otherwise you will hit the maximum level of recursion that SQL Server allows by default.

Answer 2

Seems same answer (that I thought of..) posted by other peers with great demo. However, following example and this post may help for simple and better understanding on Recursive CTE

DDL

create table temp 
(
    recid int identity (1,1)
    ,Pos_ID int
    ,Child_Pos nvarchar(50)
    ,Parent_Pos nvarchar(50)
);

insert into temp (Pos_ID, Child_Pos, Parent_Pos)
values
(1, 'Super Boss', NULL),
(2, 'Boss', 'Super Boss'),
(3, 'Sr. Mangaer 1', 'Boss'),
(3, 'Sr. Mangaer 2', 'Boss'),
(3, 'Sr. Mangaer 3', 'Boss'),
(4, 'Mangaer 1', 'Sr. Mangaer 1'),
(4, 'Mangaer 2', 'Sr. Mangaer 1'),
(4, 'Mangaer 3', 'Sr. Mangaer 2'),
(4, 'Mangaer 4', 'Sr. Mangaer 2'),
(4, 'Mangaer 5', 'Sr. Mangaer 3'),
(4, 'Mangaer 6', 'Sr. Mangaer 3'),
(5, 'Emp 01', 'Mangaer 1'),
(5, 'Emp 02', 'Mangaer 1'),
(5, 'Emp 03', 'Mangaer 2'),
(5, 'Emp 04', 'Mangaer 2'),
(5, 'Emp 05', 'Mangaer 3'),
(5, 'Emp 06', 'Mangaer 3'),
(5, 'Emp 07', 'Mangaer 4'),
(5, 'Emp 08', 'Mangaer 4'),
(5, 'Emp 09', 'Mangaer 5'),
(5, 'Emp 10', 'Mangaer 5'),
(5, 'Emp 11', 'Mangaer 6'),
(5, 'Emp 12', 'Mangaer 6')
go

Recursive CTE Example

with main as (
select Child_Pos, Parent_Pos,Pos_ID, 1 as Reculevel
from temp as t1
--where Parent_Pos is not null 

UNION ALL

select t2.Child_Pos, t2.Parent_Pos, t2.Pos_ID, main.Reculevel + 1
from temp as t2 
join main on t2.Parent_Pos = main.Child_Pos
)

select * from main

Following Recursive CTE for your Example

with main as (
select Pos, Child, Parent, Test, 1 as RecuLevel
from temp as t1

UNION ALL

select t2.Pos, t2.Child, t2.Parent, t2.Test, RecuLevel + 1
from temp as t2 
join main on t2.Parent = main.Child
)

select * from main
--option (maxrecursion 0) -- be cautious enabling this!

Answer 3

You could find material on what you are asking by searching material on Recursive Query like articles or older answers.

For creating your Recursive Query, you create a CTE where the first table is your anchor like your first level where there is column Parent is NULL. In the same CTE, you keep adding 1 to the level. Please find the answer in Fiddle

WITH MyCTE AS (
SELECT *, 1 AS Level
FROM temp
WHERE Parent IS NULL

UNION ALL

SELECT t.Pos, t.Child, t.Parent, t.Test, MyCTE.Level+1 AS Level
FROM temp AS t
INNER JOIN MyCTE
ON t.Parent = MyCTE.Child
WHERE t.Parent IS NOT NULL)
SELECT MyCTE.*, CASE WHEN Offsprings.Offspring IS NULL THEN 1 ELSE Offsprings.Offspring END AS Offspring
FROM MyCTE
LEFT JOIN (
    SELECT Parent, COUNT(Parent) AS Offspring
    FROM temp
    GROUP BY Parent)Offsprings
ON MyCTE.Child = Offsprings.Parent
ORDER BY MyCTE.Child