在sql server的select查询中获取一列的逗号分隔值
问题描述:
大家好,
我有以下要求。
这是一个两列表,记录如下:
ID名称
- ------
1 Mathi
1甘地
1 Nathan
2 Aashiq
2 Babu
3曼莫汉
3 Kumar
3 Bestha
但我想要这样的记录:
ID姓名
- ------
1 Mathi,Gandhi,Nathan
2 Aashiq,Babu
3 Manmohan,Kumar,Bestha
谢谢和问候,
Mathi。
Hi All,
I have the below requirement.
It is a two column table when the records will be as below:
ID Name
-- ------
1 Mathi
1 Gandhi
1 Nathan
2 Aashiq
2 Babu
3 Manmohan
3 Kumar
3 Bestha
But I want these records like this:
ID Name
-- ------
1 MatGandNathan
2 Aashiq,Babu
3 Manmohan,Kumar,Bestha
Thanks & Regards,
Mathi.
答
Try this,
SELECT DISTINCT ID, STUFF((SELECT ','+ CONVERT(VARCHAR(30), name)
FROM tablename AS S2
WHERE S1.ID = S2.ID
FOR XML PATH('')), 1, 1, '') AS Name
FROM tablename AS S1