Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?可能重复:SQL Server:我可以逗号将多行划分为一列吗?
I have Table X(X_ID, X_Name) is 1-M with Table Y(Y_ID, Y_Value)
我有表X(X_ID,X_Name)是1-M与表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 ?
如何使用T-Sql获得以下结果?
X_ID X_Name Y_Value
---- ------ ------
12 foo A,B
14 foo2 C,D,E
16 foo3 F,G
Thanks
1 个解决方案
#1
14
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;
#1
14
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;