select string parentitems

Posted on 2012-08-26
Last Modified: 2012-08-28
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?
Question by:johnkainn
    LVL 25

    Accepted 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.

    Author Closing Comment

    Thank you:-)

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Change variables in SQL table 6 54
    Testing connection to sql 7 35
    sql sproc 2 22
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now