使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法

DECLARE @TT TABLE (ID int,Relation varchar(25),Name varchar(25),ParentID int)
INSERT @TT SELECT 1, Great GrandFather , Thomas Bishop, null UNION ALL
SELECT 2,Grand Mom, Elian Thomas Wilson , 1 UNION ALL
SELECT 3, Dad, James Wilson,2 UNION ALL
SELECT 4, Uncle, Michael Wilson, 2 UNION ALL
SELECT 5, Aunt, Nancy Manor, 2 UNION ALL
SELECT 6, Grand Uncle, Michael Bishop, 1 UNION ALL
SELECT 7, Brother, David James Wilson,3 UNION ALL
SELECT 8, Sister, Michelle Clark, 3 UNION ALL
SELECT 9, Brother, Robert James Wilson, 3 UNION ALL
SELECT 10, Me, Steve James Wilson, 3

----------Query---------------------------------------
;WITH FamilyTree
AS(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1 FROM @TT AS Fam
INNER JOIN FamilyTree ON Fam.ParentID = FamilyTree.ID
)SELECT * FROM FamilyTree