Monday, September 25, 2006

Query hierarchical data - SQL 2005

Question:
Table data...
Line                            Name                           ForLine
1                                Stuff                          0
2                                SubStuff                       1
3                                SubSubStuff                    2
4                                Other                          0
5                                SubOther                       4
6                                LowStuff                       3
Result required...
Line          Name                           ForLine           TopName
3              SubSubStuff                 2                    Stuff
Gabbar:
   WITH MyTree (Line, Name, ForLine, TopName)
   AS
   (
   -- Anchor member definition
       SELECT
           Line,
           Name,
           ForLine,
           Name as TopName
       FROM
           MyTable
       WHERE
           ForLine = 0
       UNION ALL
   -- Recursive member definition
       SELECT
           m.Line,
           m.Name,
           m.ForLine,
           t.TopName
       FROM
           MyTable m
           INNER JOIN MyTree t
           ON m.ForLine = t.Line
   )

   SELECT
       Line,
       Name,
       ForLine,
       TopName
   FROM
       MyTree
   WHERE
       Line = 3

No comments: