Simplest way to do a recursive self-join?

by 
WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

By adding the ordering condition, you can preserve the tree order:

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

By changing the ORDER BY condition you can change the ordering of the siblings.

More Related Contents:

  1. What is the most efficient/elegant way to parse a flat table into a tree?
  2. SQL Server recursive self join
  3. SQL query for parent-child chain
  4. Sql query for tree table
  5. SQL QUERY COUNT THEN ADD
  6. SQL server command help and tutorials for specific commands?
  7. How do I UPDATE from a SELECT in SQL Server?
  8. Referring to a Column Alias in a WHERE Clause
  9. INNER JOIN vs LEFT JOIN performance in SQL Server
  10. Function vs. Stored Procedure in SQL Server
  11. How do you list the primary key of a SQL Server table?
  12. Calculating distance between two points (Latitude, Longitude)
  13. SQL Server check case-sensitivity?
  14. Multiplication aggregate operator in SQL
  15. How to update Identity Column in SQL Server?
  16. Finding duplicate rows in SQL Server
  17. Detect consecutive dates ranges using SQL
  18. Scope of temporary tables in SQL Server
  19. Convert text value in SQL Server from UTF8 to ISO 8859-1
  20. Split values over multiple rows [duplicate]
  21. How to do an inner join on row number in sql server
  22. Can an INNER JOIN offer better performance than EXISTS
  23. Why does SQL Server round off results of dividing two integers?
  24. How to Map Input and Output Columns dynamically in SSIS?
  25. Find the real column name of an alias used in a view?
  26. SQL Server: should I use information_schema tables over sys tables?
  27. How to create a pivot query in sql server without aggregate function
  28. SQL Server Cumulative Sum by Group
  29. SQL Server FOR XML Path make repeating nodes
  30. What’s the correct name for an “association table” (a many-to-many relationship) [closed]

Leave a Comment