Displaying nested parent-child relationships

Question

Displaying nested parent-child relationships

I have a table as shown below with some data:

ID        Name       ParentID
-----------------------------
1         A          NULL
2         B          NULL
3         C          1
4         D          2
5         E          3

As you can see, E is a child of C, which is a child of A. I'm looking for a simple SQL query that can return a string like the one below given the child ID, for example. ID = 5, that is, E must have the following:

Data
-----
A -> C -> E

I tried the following query but got stuck along the way

SELECT a.ID,
       a.Name,
       COALESCE(b.Name, '->') AS 'ParentName'
FROM MyTable AS a
LEFT JOIN MyTable  AS b ON a.ID = b.ID
WHERE a.ID = 5

Any help would be appreciated.

+3

sql-server tsql recursion

Bat_Programmer May 26 '15 at 22:08

source to share

1 answer

Giorgi nakeuri · Accepted Answer · 2015-05-26T22:19:48+0000

Try the following:

with cte as(
  select * from t where id = 5
  union all
  select t.id, t.name, t.pid from cte c
  join t on c.pid = t.id)
select replace(stuff((select '-' + name from cte
order by id
for xml path('')), 1, 1, ''), '-', '->')

Fiddle http://sqlfiddle.com/#!3/6fdde1/19

Displaying nested parent-child relationships

More articles: