How can I combine multiple rows into a comma-delimited list in SQL Server? [duplicate]
Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?
I have Table X(X_ID, X_Name) is 1-M with Table Y(Y_ID, Y_Value)
Table X:
X_ID X_Name
---- ------
12 foo
14 foo2
16 foo3
Table Y:
X_ID Y_Value
---- -------
12 A
12 B
14 C
14 D
14 E
16 F
16 G
How to get the following result using T-Sql ?
X_ID X_Name Y_Value
---- ------ ------
12 foo A,B
14 foo2 C,D,E
16 foo3 F,G
Thanks
Solution 1:
SELECT X.X_ID, X.X_Name, Y_Values = STUFF((SELECT N',' + Y_Value FROM dbo.Y
WHERE Y.X_ID = X.X_ID
FOR XML PATH(''),
TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'')
FROM dbo.X;
In SQL Server 2017 and Azure SQL Database, you can use the new STRING_AGG()
function:
SELECT x.X_ID, x.X_Name,
Y_Values = STRING_AGG(Y.Y_Value,',')
FROM dbo.X
INNER JOIN dbo.Y
ON X.X_ID = Y.X_ID
GROUP BY x.X_ID, x.X_Name;
If you don't like the default order, you can specify it using WITHIN GROUP
:
SELECT x.X_ID, x.X_Name,
Y_Values = STRING_AGG(Y.Y_Value,',') WITHIN GROUP (ORDER BY Y.Y_Value)
FROM dbo.X
INNER JOIN dbo.Y
ON X.X_ID = Y.X_ID
GROUP BY x.X_ID, x.X_Name;