Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Concatenation 1

Status
Not open for further replies.

Zurich98

Programmer
Apr 8, 2006
64
US
Hello All,

The db is SQL Server 2005.

I have the following data in a temp table. What I'm trying to accomplish is to concatenate the CourseCode for OrderID = 111111 so that the CourseCode will be something like HRD8319,PSY8875

OrderID CourseCode ProductID
111111 HRD8319 42617
111111 PSY8875 42629

The desire result is as follow

OrderID CourseCode
111111 HRD8319,PSY8875

Is this possible? Any suggestion/input is greatly appreciated.

Thanks in advance
 
Thank you so much for the link. It gave me exactly what I need.

SELECT
t1.OrderID,
CourseCode = SUBSTRING((SELECT ( ', ' + CourseCode )
FROM #URAll t2
WHERE t1.OrderID = t2.OrderID
ORDER BY OrderID,CourseCode
FOR XML PATH( '' )
), 3, 1000 )FROM #URAll t1
GROUP BY OrderID
ORDER BY OrderID

I don't understand the following line of code

FOR XML PATH( '' ) ???
, 3, 1000) --probably for the SUBSTRING function???

Would you please explain what the code is for. Thanks
 
Substring is to remove the first 2 characters (', '). Try running without substring and see the result. 1000 is assuming that the column length would not exceed 1000 characters. You may want to use bigger number here.

Also please check the second blog post - IMHO, it has really great explanation of the inner logic.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top