info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
|
Concatenate Row Values using T-SQL
by BF (Principal Consultant; Architecture; Engineering)
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:
|
|
|
|
|
|
|
|
|
|