Concatenation 1

[Zurich98]

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

 

[markros]

See

http://code.msdn.microsoft.com/SQLE...CreateACommaDelimitedList&referringTitle=Home

 

[markros]

Here is another blog on this topic that discussed this issue in depth (took me some time finding it):

http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

 

[Zurich98]

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

 

[markros]

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.

 

[Zurich98]

Markos, Thank you very much for your helps.