info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Migrations, Replication & Cloud



Concatenate Row Values using T-SQL
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2015-06-21






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: