SQL 多行到一个逗号分隔值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21760969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 01:03:31 来源:igfitidea点击:
Multiple rows to one comma-separated value
提问by Sanjeev Singh
I want to create a table valued function in SQL Server, which I want to return data in comma separated values.
我想在 SQL Server 中创建一个表值函数，我想以逗号分隔值返回数据。
For example table: tbl
例如表： tbl
ID | Value
---+-------
1 | 100
1 | 200
1 | 300
1 | 400
Now when I execute the query using the function Func1(value)
现在当我使用函数执行查询时 Func1(value)
SELECT Func1(Value)
FROM tbl
WHERE ID = 1
Output that I want is: 100,200,300,400
我想要的输出是： 100,200,300,400
回答by M.Ali
Test Data
测试数据
DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)
Query
询问
SELECT ID
,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
FROM @Table1
WHERE ID = t.ID
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID
Result Set
结果集
╔════╦═════════════════════╗
║ ID ║ List_Output ║
╠════╬═════════════════════╣
║ 1 ║ 100, 200, 300, 400 ║
╚════╩═════════════════════╝
