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



image


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

4 comments:

chetan said...

http://mycodingexperience.blogspot.com/2011/04/common-table-expression-cte-and-tree.html

Anonymous said...

Great post! Just what I needed, thanks!

Laci said...

Hi,
What if I wanted to connect the Great GrandFather to the Brother? How can I make a new output where Thomas Bishop and Robert James Wilson are in the same row?

Anonymous said...

Very Good Article...