Recursive Child/Parent queries in T/SQL

by

You’ll need your recursion to also build something that can be sorted by at the end:

declare @t TABLE (
[CHILD] [int] NOT NULL,
[PARENT] [int] NOT NULL
) 

insert @t values
( 0, -1),   -- I added this
( 1, 2 ),
( 2, 0 ),
( 3, 1 ),
( 4, 2 ),
( 5, 0 )

(note I have added a true root element)

;with n(CHILD, PARENT, GENERATION, hierarchy) as (
select CHILD, PARENT,0, CAST(CHILD as nvarchar) as GENERATION from @t
where PARENT=-1
union all
select nplus1.CHILD, nplus1.PARENT, GENERATION+1, 
cast(n.hierarchy + '.' + CAST(nplus1.child as nvarchar) as nvarchar)
 from 
@t as nplus1 inner join n on nplus1.PARENT=n.CHILD 
)
select CHILD,GENERATION
from n
order by hierarchy

returns

CHILD       GENERATION
----------- -----------
0           0
2           1
1           2
3           3
4           2
5           1

Including the hierarchy for illustration:

CHILD       GENERATION  hierarchy
----------- ----------- ------------------------------
0           0           0
2           1           0.2
1           2           0.2.1
3           3           0.2.1.3
4           2           0.2.4
5           1           0.5

Depending on how big your ids get, you might have to do stuff with left-padding with zeroes to get the sorting right.

Note that SQL 2008 has a built-in hierarchy type for this kind of thing…

More Related Contents:

  1. T-SQL: Export to new Excel file
  2. SQL MERGE statement to update data
  3. How to get the last record per group in SQL
  4. T-SQL substring – separating first and last name
  5. How to get week number of the month from the date in sql server 2008
  6. T-SQL strip all non-alpha and non-numeric characters
  7. Avoiding IF ELSE due to variable been NULL
  8. to select all the employees who have joined most recently under king
  9. Unpivot with column name
  10. Get first day of week in SQL Server
  11. How to connect to local instance of SQL Server 2008 Express
  12. How to group time by hour or by 10 minutes
  13. Convert varchar into datetime in SQL Server
  14. Export query result to .csv file in SQL Server 2008
  15. Check if a row exists, otherwise insert
  16. Confused about UPDLOCK, HOLDLOCK
  17. TSQL: How to convert local time to UTC? (SQL Server 2008)
  18. How can I load a large flat file into a database table using SSIS?
  19. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  20. How does this CASE expression reach the ELSE clause?
  21. TransactSQL to run another TransactSQL script
  22. How to convert number of minutes to hh:mm format in TSQL?
  23. How can I group time by hour or by 10 minutes?
  24. SQL Server: Split operation
  25. How do I move a table into a schema in T-SQL
  26. Using IF ELSE statement based on Count to execute different Insert statements
  27. Restrict SQL Server Login access to only one database
  28. Repeat Rows N Times According to Column Value
  29. Select rows where column value has changed
  30. Forcing code-first to always initialize a non-existent database?

Leave a Comment