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
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
In SSMS you can hold SHIFT+ALT and then select multiple lines with a mouse or go up and down with the arrow/cursor keys to edit across multiple lines, and add the comma.
To arrange similar (identical) data into groups, we use SQL GROUP BY clause. The SQL GROUP BY clause is used along with some aggregate functions to group columns that have the same values in different rows. We generally use the GROUP BY clause with the SELECT statement, WHERE clause, and ORDER BY clauses.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With