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 )