CODEWITHSUNDEEP
sql-servert-sqlrecursion
Bat_Programmer
Bat_Programmer

Reputation: 6851

Displaying nested parent-child relationship

I have a table as 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 am looking for a simple SQL query which can return a string like below given a child ID, for e.g. ID = 5, which is E should have something as below:

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

I have tried below query but stuck on way forward

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 assistance would be appreciated.

Upvotes: 3

Views: 894

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Try this:

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

Upvotes: 4

Related Questions