recursive select statement

  • Hey guys,

    I have a pretty easy one for you experts. I have a table with this structure:

    idint

    namenvarchar(50)

    parent_idint

    I want to get a list of all the names and concatenate the names when the parent_id = the id of a previous record all the way until the parent_id is equal to 0 (0 meaning you are at the root).

    What is the query statement to get such a result set?

    Thank you.

  • So you basically want a list of who person a manages all nicely concatenated together like

    Fred

  • Sorry used the tab key

    Like

    Fred John, Bert, Andy

    Bert Andy

    Is that the sort of thing you want ?

  • Correct, in this instance they are geographically regions.

    so

    ID name parent id

    204Arizona 9

    463Greater Phoenix Area204

    474Northern Arizona 204

    478Southern Arizona 204

    481Tucson Area 204

    483Western Arizona 204

    which will produce a result set of Tucson Area, Arizona, etc...

  • try this

    WITH CTE AS (

    SELECT id,name,parent_id, 1 AS Level, CAST(name AS VARCHAR(MAX)) AS FullPath

    FROM mytable

    WHERE parent_id = 0

    UNION ALL

    SELECT t.id,t.name,t.parent_id, c.Level+1, CAST(t.name AS VARCHAR(MAX)) +',' + c.FullPath

    FROM mytable t

    INNER JOIN CTE c ON c.id=t.parent_id

    )

    SELECT FullPath

    FROM CTE

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • That did it. Now to figure out and understand just exactly what you did. I love learning new stuff. Thank you.

  • Or obviously if you wanted to reverse the data so top level name first you could use

    WITH CTE AS (

    SELECT id,name,parent_id, 1 AS Level, CAST(name AS VARCHAR(MAX)) AS FullPath

    FROM mytable

    WHERE parent_id = 0

    UNION ALL

    SELECT t.id,t.name,t.parent_id, c.Level+1, c.FullPath + ',' + CAST(t.name AS VARCHAR(MAX))

    FROM mytable t

    INNER JOIN CTE c ON c.id=t.parent_id

    )

    SELECT Name, FullPath

    FROM CTE

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply