If you have a table containing hierarchical data (e.g. a parent having children) and you want to show all the children and grand children for a person you can use a "Common Table Expression" in Microsoft SQL Server 2008.

 

Table Person

image

 

Table Person Data

truncate table person
insert into person (Id, Name, Birthday, ParentId) values (1, 'John Do', '1950-6-1', null)
insert into person (Id, Name, Birthday, ParentId) values (2, 'Carl Do', '1980-3-5', 1)
insert into person (Id, Name, Birthday, ParentId) values (3, 'Michael Do', '2010-12-12', 2)
insert into person (Id, Name, Birthday, ParentId) values (4, 'Tonny Jackson', '1915-3-3', null)
insert into person (Id, Name, Birthday, ParentId) values (5, 'Bo Jackson', '1945-7-7', 4)
insert into person (Id, Name, Birthday, ParentId) values (6, 'Harry Jackson', '1981-4-4', 5)
insert into person (Id, Name, Birthday, ParentId) values (7, 'Bart Jackson', '2009-12-11', 6)

image

 

 

Returning a person with all it’s children and grandchildren

declare @PersonId int
set @PersonId = 4

-- Define the "Common Table Expression" with name "person_cte"
; with person_cte as (
 
 -- Select all the direct children of the given person
 select        p2.Id,
             p2.Name,
            p2.Birthday,
            p2.ParentId
 from        person p1
 inner join person p2 on p2.ParentId = p1.Id
 where        p1.Id = @PersonId 

 union all

 -- Add grandchildren's, by recursively calling this "Common Table Expression"
 select        p1.Id,
            p1.Name,
            p1.Birthday,
            p1.ParentId
 from        person_cte p2
 inner join person p1 on p1.ParentId = p2.Id
 
) 
select
    Id,
    Name,
    Birthday,
    ParentId
from person_cte

union all

-- Add the given person to the result
select
    Id,
    Name,
    Birthday,
    ParentId
from person
where Id = @PersonId
order by Birthday

Result

image

 

Background information:

Using Common Table Expressions: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Recursive Queries Using Common Table Expressions: http://msdn.microsoft.com/en-us/library/ms186243.aspx

One Comment

  1. Your example is close to my situation, but I need further help, if you can.

    I have a table O with id and parent id’s that lists division, state, and branch org levels, with a org name for each. The detail table S lists sales with a single column referencing the org id. I need to report on sales by branch, by state, and by division, and am trying to use O1 and O2 as aliases for the same table to write a dataset query. Something like:

    select O2.org_name
    from O1 inner join
    O2.org_id on O1.org_id=02.org_parent_id
    where O2.org_id=S.sales_org_id

    But it doesn’t work. How should I do this?

    Thanks for your help!
    Robin

    Robin Merrill

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.