select string parentitems

I have a table with the columns: Id, ParentId, MyText

I would like to get all parentitems in a string, i.e.

Id=100, ParentId=50, MyText='House'
Id=50, ParentId=30, MyText='Boat'
Id=40, ParentId=20, MyText='Truck'
Id=20, ParentId=0, MyText='Bicycle'

The result should be a string: 'Boat, Truck, Bicycle'

How is best to do that?
Who is Participating?
"Best" is a very subjective thing at the best of time.  Here is one method.  btw your example has 'Truck' as not being a parent ... I assumed you meant 'Boat' to have a parentid of 40.
;with test_data as (
select 100 as Id, 50 as ParentId, 'House'   as MyText union all
select 50  as Id, 40 as ParentId, 'Boat'    as MyText union all
select 40  as Id, 20 as ParentId, 'Truck'   as MyText union all
select 20  as Id, 0  as ParentId, 'Bicycle' as MyText
select stuff((SELECT ', ' + MyText 
              FROM test_data
              WHERE Id IN (select ParentId from test_data t2)
              ORDER BY Id desc
              FOR XML PATH ('')),
             1, 2, '') as final_string

Open in new window

The cte ("with test_data as ...") is purely there to create the test data for my SQL.
johnkainnAuthor Commented:
Thank you:-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.