How to concate multi rows to a column

Question

How to concate multi rows to a column

krunalpanchal33

SSC Rookie

Points: 27
More actions
January 31, 2011 at 8:23 pm

#246037

Hello Team,
I've a simple query to be solved in SQL server 2005 as I am new to SQL server I couldn't figure out the way. I've the following table and rows.
ID NAME ParentID
1 A 0
2 B 0
3 C 1
4 D 2
5 E 1
My query to display above table's data as the below way.
ID NAME
1 A
2 B
3 A-C
4 B-D
5 A-E
if ParentID is 0 >> just display it's ID and NAME
if ParentID !=0 >> particular row ID, NAME where ID=ParentID - NAME from particular row
Please give me a solution to do this. I don't want to use CURSOR.
My basic logic is to create a TEMP table with same field names... use WHILE loop and insert to TEMP table... but this all logic is only my imagination... I don't know how to Implement it. Very Thankful to you in advance for the solution.

Viewing 2 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

What you're requested is known as a "Hierarchical path" and it can be done in a single query using a "Recursive CTE". Please look that term up in Books Online for how it works. DO keep in mind that it's not really any more effective than a simple While Loop and, depending on what you're doing in the rCTE, can be much worse especially when it comes to reads.

--===== Create a test table to hold the data. This is NOT a part of the solution

CREATE TABLE #TestTable (ID INT, NAME VARCHAR(10), ParentID INT)

INSERT INTO #TestTable

(ID,NAME,ParentID)

SELECT '1','A','0' UNION ALL

SELECT '2','B','0' UNION ALL

SELECT '3','C','1' UNION ALL

SELECT '4','D','2' UNION ALL

SELECT '5','E','1'

--===== Build the hierarchical path requested

WITH

cteBuildPath AS

(

SELECT ID, Name = CAST(Name AS VARCHAR(MAX))

FROM #TestTable

WHERE ParentID = 0

UNION ALL

SELECT tt.ID, Name = cte.Name + '-' + tt.Name

FROM #TestTable tt

INNER JOIN cteBuildPath cte

ON tt.ParentID = cte.ID

)

SELECT * FROM cteBuildPath ORDER BY ID

Also keep in mind that if your original ID/ParentID list isn't perfect (ie, it has a cyclic reference), the code above will fail.

Also, although it's not likely that you'll need more than 100 iterations (the hierarchical bill of materials for a 747 only has something like 18 levels in it) unless you're running something like an MLM, you really do need to read up on Recursive CTEs so you can find out about things like MAXRECURSION settings if you need to go past 100 iterations.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)