• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

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?
1 Solution
"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:-)

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now