How do I create a recursive query in MSSQL 2005?

database, recursion, sql, sql-server

Solution

On SQL Server 2005 you can use CTEs (Common Table Expressions) :

with Hierachy(CustomerID, ParentID, Name, Level)
as
(
select CustomerID, ParentID, Name, 0 as Level
    from Customers c
    where c.CustomerID = 2 -- insert parameter here
    union all
    select c.CustomerID, c.ParentID, c.Name, ch.Level + 1
    from Customers c
    inner join Hierachy ch
    on c.ParentId = ch.CustomerID
)
select CustomerID, ParentID, Name
from Hierachy
where Level > 0

Problem

Let's say I have the following table: ``` CustomerID ParentID Name ========== ======== ==== 1 null John 2 1 James 3 2 Jenna 4 3 Jennifer 5 3 Peter 6 5 Alice 7 5 Steve 8 1 Larry ``` I want to retrieve in one query all the descendants of James (Jenna,Jennifer,Peter, Alice, Steve). Thanks, Pablo.

Original source