SQL Data Hierarchy

Dynamic T-SQL query for hierarchical data

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.

Write a query to get hierarchical data in SQL

Use Recusrion for this purpose
SELECT * INTO EmployeeDetails FROM 
(
    SELECT Code = 1  ,ParentCode = NULL, EmployeeName  = 'Hemant'
    UNION ALL
    SELECT Code = 2  ,ParentCode = 1, EmployeeName  = 'Sachin'
    UNION ALL
    SELECT Code = 3  ,ParentCode = 2, EmployeeName  = 'Shanti'
    UNION ALL
    SELECT Code = 4  ,ParentCode = 1, EmployeeName  = 'Sandesh'
    UNION ALL
    SELECT Code =  5  ,ParentCode = 3, EmployeeName  = 'Sandeep'
    UNION ALL
    SELECT Code = 6  ,ParentCode = NULL, EmployeeName  = 'Smily'
    UNION ALL
    SELECT Code = 7  ,ParentCode = 6, EmployeeName  = 'Snehi'
    UNION ALL
    SELECT Code =  8  ,ParentCode = 6, EmployeeName  = 'Kiran'
    UNION ALL
    SELECT Code =  9  ,ParentCode = 7, EmployeeName  = 'Shital'
    UNION ALL
    SELECT Code =  10  ,ParentCode = 9, EmployeeName  = 'Simran'
) X

//This is CTE

WITH REC
(Code,ParentValue,EmployeeName,Parent,Level,GRP,LEVELs,[ORDER])
AS
(
SELECT Code,ParentCode ParentValue,EmployeeName,CONVERT(VARCHAR(max),Null) Parent,
1 level ,Code GRP,STR(0) LEVELs,
CAST(ROW_NUMBER()OVER(PARTITION BY ParentCode ORDER BY ParentCode) AS VARCHAR(MAX)) as [ORDER]
FROM EmployeeDetails WHERE ParentCode is null
UNION ALL
SELECT B.Code,b.ParentCode ParentValue,b.EmployeeName,CONVERT(VARCHAR(max),A.EmployeeName) Parent,
 A.level + 1 Level , A.GRP,STR(ROW_NUMBER() OVER(PArtition By B.ParentCode order by B.ParentCode)) LEVELs,
[ORDER] + CAST(ROW_NUMBER()OVER(PARTITION BY B.ParentCode ORDER BY B.ParentCode) AS VARCHAR(MAX)) AS [ORDER]
FROM EmployeeDetails  B 
INNER JOIN REC A ON B.ParentCode = A.Code
)
SELECT*
FROM REC A
ORDER BY A.[ORDER]

Related Topics