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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment