Concatenate Row Values in Microsoft SQL Server using T-SQL's FOR XML and STUFF function.
MSDN:
STUFF() FUNCTION:
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
FOR XML:
You can execute SQL queries to return results as XML instead of standard rowsets. These queries can be executed directly or executed from within stored procedures and user-defined functions. To retrieve the results directly, first use the FOR XML clause of the SELECT statement. Then, within the FOR XML clause, specify an XML mode: RAW, AUTO, EXPLICIT or PATH.
T-SQL
SELECT t3.PostID,
STUFF(
(SELECT ',' + t1.LabelName --Add a comma before each value
FROM dbo.POSTS T2 INNER JOIN dbo.LABELS T1 on t1.LabelID=t2.LabelID where t2.PostID = t3.PostID
FOR XML PATH(')), --Select as XML
1, 1,') --Remove the first character (,) from the results set. Part of STUFF()
AS Labels
FROM dbo.POSTS t3
GROUP BY t3.PostID
Image 1:
Image 2:
Image 3:
|
|
|
|
|
|