Recursive CTE's

First, the problem: You have a table containing some kind of a hierarchy using parent IDs. You have to write some SQL to return all of this data in the hierarchy with each parent and then the child records.

One way of doing this would have been to use a loop, starting at the top of the hierarchy, working your way down. SQL Server 2008 provides a new kind of Common Table Expression (CTE), called a recursive CTE. It consists of two parts:

• An anchor query, which is the source of the recursion, along with a UNION ALL statement and a second query, with recurses across the anchor query
• An outer select that references the routine and specifies the number of recursion levels

Table Create and Sample Data:

create table Employee
{
  EmployeeID int,
  ManagerID  int,
  FullName   varchar(50)
}
go

insert Employee values(0, 1,  'CEO')
insert Employee values(1, 2,  'Marketing Director')
insert Employee values(1, 3,  'IT Director')
insert Employee values(2, 4,  'Marketing Manager')
insert Employee values(4, 5,  'Sales Analyst 1')
insert Employee values(4, 6,  'Sales Analyst 2')
insert Employee values(3, 7,  'Systems Manager')
insert Employee values(3, 8,  'Hardware Manager')
insert Employee values(7, 9,  'Developer')
insert Employee values(7, 10, 'Tester')
insert Employee values(8, 11, 'Technician')
insert Employee values(8, 12, 'Telephone Specialist')
go

The CTE:

with EmployeeCTE as
(
  -- The Anchor Query
  select emp.EmployeeID,
         emp.ManagerID,
         '' as 'Manager',
         emp.FullName
    from Employe emp with (nolock)
   where ManagerID = 0
  UNION ALL
  select emp.EmployeeID,
         emp.ManagerID,
         cte.FullName as 'Manager',
         emp.FullName
    from EmployeeCTE cte
            join
         Employee    emp with (nolock) on emp.ManagerID = cte.EmployeeID
)

select *
  from EmployeeCTE