SQL Server Parent / Child Ordering (Possible recursion?)

Question

I would like to generate a query that lists the parent node with each of its child nodes and nodes (etc.) below each other. Just as you would structure a comment with an answer and an answer to that.

For example, given this data:

ID ParentID -------------- 1 0 2 0 3 2 4 2 5 0 6 1 7 2 8 7 

I would like to receive a query structured as follows:

 ID ParentID -------------- 1 0 6 1 2 0 3 2 4 2 7 2 8 7 5 0 

I know that I need a recursive CTE, I believe, but how?

thanks

Answer 1

It looks like you want to do DFS (Depth First Search) in a tree structure stored in a table.

  0 / | \ / | \ / | \ / | \ 1 2 5 | / | \ 6 3 4 7 | 8 

The logic of your code:

 DFS(Node p){ children = GetChildren(p) foreach (c in children) { Print("ID: " + c + " Parent ID: " + p) DFS(c) } } 

Now SQL Server. Choosing nodes that are directly children of this node is simple:

 SELECT [ID] FROM [Table] WHERE [ParentID] = @parentID 

Create a temporary table to store the results:

 CREATE TABLE #temp(ID int, ParentID int); 

Then either recursively select SELECT INTO in the result table, or do it in a while loop.

Answer 2

SQL2008 +

 DECLARE @MyTable TABLE ( ID INT PRIMARY KEY, ParentID INT NOT NULL ); INSERT @MyTable (ID, ParentID) SELECT 1, 0 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 2 UNION ALL SELECT 4, 2 UNION ALL SELECT 5, 0 UNION ALL SELECT 6, 1 UNION ALL SELECT 7, 2 UNION ALL SELECT 8, 7; WITH ConvertAdiacentListToHierarchyID AS ( SELECT x.ID, x.ParentID, CONVERT(VARCHAR(8000), '/' + CONVERT(VARCHAR(10), x.ID) + '/') AS Node FROM @MyTable x WHERE x.ParentID = 0 UNION ALL SELECT x.ID, x.ParentID, CONVERT(VARCHAR(8000), y.Node + CONVERT(VARCHAR(10), x.ID) + '/') AS Node FROM @MyTable x INNER JOIN ConvertAdiacentListToHierarchyID y ON x.ParentID = y.ID ) SELECT cte.*, CONVERT(HIERARCHYID, cte.Node) AS NodeAsHierachyID FROM ConvertAdiacentListToHierarchyID cte ORDER BY NodeAsHierachyID; 

Results:

 ID ParentID Node NodeAsHierachyID -- -------- ------- ---------------- 1 0 /1/ 0x58 6 1 /1/6/ 0x5CA0 2 0 /2/ 0x68 3 2 /2/3/ 0x6BC0 4 2 /2/4/ 0x6C20 7 2 /2/7/ 0x6CE0 8 7 /2/7/8/ 0x6CF440 5 0 /5/ 0x8C 

Answer 3

Try something like this ...

 ;WITH cte (EmpID, ParentID, EmpLevel) AS ( SELECT ID, ParentID, 1 FROM Table WHERE ParentID = 0 UNION ALL SELECT t.ID, t.ParentID,c.EmpLevel + 1 FROM Table t INNER JOIN cte c ON t.ParentID = c.EmpID ) SELECT EmpID,EmpLevel FROM cte ORDER BY EmpID,EmpLevel