SQLSERVER2008CTE生成结点的FullPath

SQL SERVER 2008 使用CTE是经常的事儿，有时我们想存储一些冗余数据，像每个结点的FullPath。

DECLARE @tbl TABLE 
( 
Id int 
,ParentId int 
) 
INSERT INTO @tbl 
( Id, ParentId ) 
VALUES ( 0, NULL ) 
, ( 8, 0 ) 
, ( 12, 8 ) 
, ( 16, 12 ) 
, ( 17, 16 ) 
, ( 18, 17 ) 
, ( 19, 17 ) 

WITH abcd 
AS ( 
-- anchor 
SELECT id 
,ParentID 
,CAST(id AS VARCHAR(100)) AS [Path] 
FROM @tbl 
WHERE ParentId IS NULL 
UNION ALL 
--recursive member 
SELECT t.id 
,t.ParentID 
,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path] 
FROM @tbl AS t 
JOIN abcd AS a ON t.ParentId = a.id 
) 
SELECT Id ,ParentID ,[Path] 
FROM abcd 
WHERE Id NOT IN ( SELECT ParentId 
FROM @tbl 
WHERE ParentId IS NOT NULL ) 
 
返回： 
Id ParentID Path 
----------- ----------- ---------------------- 
18 17 0,8,12,16,17,18 
19 17 0,8,12,16,17,19 
就这么简单，实际上有Sql server 2008中HierarchyType 也能很好的解决这个问题。我将在后面写一些关于HierarchyType的Post. 

希望这篇POST对您有帮助。 

Author Peter Liu