55
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)

Kld
  • 6,970
  • 3
  • 37
  • 50

2 Answers2

108
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

  • Very slick. I was actually looking for a recursive function to put in stored procedure to do this very thing. – Bruce C Dec 02 '14 at 19:08
  • @a_horse_with_no_name - Well played, sir. – Buggieboy Jun 16 '15 at 18:08
  • 2
    This doesn't seem to work at all - returns only the first row. What am I missing? – Andrey Sep 24 '15 at 08:16
  • 6
    @Andrey: you are right. The join for the recursive part was the wrong way round. See the update (and the SQLFiddle). Interesting that nobody noticed this in over two years... –  Sep 24 '15 at 09:17
  • And just swap p and c if you need to get the children: c.parent_id = p.id – Nikolai Koudelia Jan 17 '17 at 14:02
  • 2
    Needs "with recursive name_tree" to work in modern Postgres – BeRecursive May 31 '18 at 11:49
  • 2
    @BeRecursive: you are correct `recursive` is indeed required by the SQL standard (and Postgres) - however Microsoft chose to ignore the standard when they implemented that feature in SQL Server and `recursive` will result in an error there - and the question is about SQL Server –  May 31 '18 at 12:15
  • SQLFiddle for Postgres: http://sqlfiddle.com/#!17/87d0c/13/0 – Raman Oct 21 '21 at 13:18
  • 1
    You can also get all the descendants by changing `p.parent_id = c.id` to `p.id = c.parent_id`. Magic :) – Ghis Dec 15 '21 at 23:12
15

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.

Ian Preston
  • 38,816
  • 8
  • 95
  • 92