;With CTE
AS
(
SELECT key1,key2,[text],CAST(key1 as varchar(max)) as [path],CAST(1 AS int) AS level
FROM table
WHERE key2 IS NULL
UNION ALL
SELECT t.key1,t.key2,t.[text],CAST(c.[path] + '\' + CAST(t.key1 AS varchar(max)) AS varchar(max)),c.Level + 1
FROM table t
INNER JOIN CTE c
ON c.key1 = t.key2
)
SELECT key1,key2,[text]
FROM CTE
ORDER BY LEFT([path],CHARINDEX('\',[path] + '\')-1),Level
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
I need help in formulating a query
Posted - 07/24/2012 : 10:16:01
