多行到一个逗号分隔的值[重复]

This question already has an answer here:

这个问题已经有了答案:

How to concatenate text from multiple rows into a single text string in SQL server? 43 answers
如何将多个行中的文本连接到SQL server中的单个文本字符串中?43个答案
How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate] 13 answers
如何创建SQL Server函数，将子查询中的多个行“连接”到单个分隔字段?(重复)13的答案

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

例如表:台

ID | Value
---+-------
 1 | 100
 1 | 200
 1 | 300     
 1 | 400

Now when I execute the query using the function Func1(value)

现在，当我使用函数Func1(值)执行查询时

SELECT Func1(Value) 
FROM tbl 
WHERE ID = 1

Output that I want is: 100,200,300,400

我想要的输出是:100,200,300,400

1 个解决方案

#1

182

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 ║
╚════╩═════════════════════╝

#1

182