Recursive query that returns itself and all of its descendants

Question

My Table 'Levels' in a SQL Server 2005 database looks like this:

LevelId Description      ParentLevelId
0       Level_1          NULL
1       Level_2          0
2       Level_3          0
3       Level_4          1
4       Level_5          3

I now want to create a query that will produce a results set in which every level and all of its children, grand-children etc. are returned. So basically I want to return an output like this:

LevelId Description DescendantId Descendant_Description
0       Level_1     0            Level_1
0       Level_1     1            Level_2
0       Level_1     2            Level_3    
0       Level_1     3            Level_4   
0       Level_1     4            Level_5    
1       Level_2     1            Level_2
1       Level_2     3            Level_3
1       Level_2     4            Level_5
2       Level_3     2            Level_3
3       Level_4     3            Level_4
4       Level_5     4            Level_5 

Oddly enough I wrote a similar query today where all levels and all of its ancestors are shown. Somehow I'm stuck writing a similar query for something which is 'the other way around'. Anyone any ideas?

Answer 1

The easiest way is to write a recursive CTE. Read this article for more details: Common Table Expressions

Answer 2

WITH    q (LevelId, Description, DescendantId, Descendant_Description) AS
        (
        SELECT  LevelId, Description, LevelId, Description
        FROM    mytable
        UNION ALL
        SELECT  t.LevelId, t.Description, q.DescendantId, q.Descendant_Description
        FROM    q
        JOIN    mytable t
        ON      t.ParentLevelId = q.LevelId
        )
SELECT  *
FROM    q
ORDER BY
        LevelId, DescendantId

Since this query returns all ancestor-descendant pairs in the system (builds a such called transitive closure), all you need to put it the other way round is to swap the fields and change the ordering:

WITH    q (LevelId, Description, DescendantId, Descendant_Description) AS
        (
        SELECT  LevelId, Description, LevelId, Description
        FROM    mytable
        UNION ALL
        SELECT  t.LevelId, t.Description, q.DescendantId, q.Descendant_Description
        FROM    q
        JOIN    mytable t
        ON      t.ParentLevelId = q.LevelId
        )
SELECT  DescendantId AS LevelId, Descendant_Description AS Description,
        LevelId AS DescendantId, Description AS Descendant_Description
FROM    q
ORDER BY
        LevelId, DescendantId