A recursive query that returns itself and all its descendants

Question

My Levels table 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

Now I want to create a query that will create a result set in which each level and all its children, grandchildren, etc. will be returned. So basically I want to return the output as follows:

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, which shows all levels and all its ancestors. Somehow, I'm stuck in writing a similar query for something that is "vice versa." Any ideas?

Answer 1

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

-- ( ), , -

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

Answer 2

- CTE. :