A Simple Family Tree Query Using Recursive CTE’s in SQL Server 2005/2008

I was recently working with one  of  my colleagues Steve on a Family Tree query. His software collected information about Relations of a person and then displayed it in a TreeView. We started discussing about Recursive Common Table Expressions(CTE) and how well CTE’s fit into such requirements.  I will give you an example of how we used Recursive CTE’s to get the Parent and Generation Level of each person in the Family Tree. One of the tables in his software looked similar to the following -

Note: A Family Tree usually contains many more columns than the one shown here. I have reduced the columns of the original table for understanding purposes. We are also considering only one of the Parents (Father or Mother) of each person in this query

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 

Here’s the query to find the Parent and Generation Level using Recursive CTE’s

;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

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook