recursive select statement
-
Hey guys,
I have a pretty easy one for you experts. I have a table with this structure:
id int
name nvarchar(50)
parent_id int
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
204 Arizona 9
463 Greater Phoenix Area 204
474 Northern Arizona 204
478 Southern Arizona 204
481 Tucson Area 204
483 Western 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