SQL recursive query that gets all ancestors of an item

Question

ID       parent_id   name --------------------- 1        2            first  2        4            second 3        3            third 4        5            fourth 5        -           fifth 

Ancestors list of first should be (2, 4, 5)

Answer 1

with name_tree as (    select id, parent_id, name    from the_unknown_table    where id = 1 -- this is the starting point you want in your recursion    union all    select c.id, c.parent_id, c.name    from the_unknown_table c      join name_tree p on p.parent_id = c.id  -- this is the recursion )  select * from name_tree where id <> 1; -- exclude the starting point from the overall result 

SQLFiddle: http://sqlfiddle.com/#!3/87d0c/1

Answer 2

You can use something like this:

with parents as  (   select ID, parent_ID   from t   where parent_ID is not null   union all    select p.ID, t.parent_ID   from parents p     inner join t on p.parent_ID = t.ID       and t.parent_ID is not null       and t.ID <> t.parent_ID ) select *   , parents = '(' + stuff     (       (         select ', ' + cast(p.parent_ID as varchar(100))         from parents p          where t.ID = p.ID         for xml path('')       ), 1, 2, ''     ) + ')' from t order by ID 

SQL Fiddle with demo.

This combines two very common T-SQL techniques - using a CTE to get a hierarchy and using FOR XML PATH to get a CSV list.