Geek asks and answers

How to combine multiple lines in a comma separated list in SQL Server?

Possible duplicate:
SQL Server: can I rewrite multiple rows into one column?

I have table X (X_ID, X_Name) - 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

thank

+5
source share
1 answer
SELECT X.X_ID, X.X_Name, Y_Value = STUFF((SELECT ',' + Y_Value FROM dbo.Y
  WHERE Y.X_ID = X.X_ID
  FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
  FROM dbo.X;
+14
source

More articles:


All Articles