多个行到一个逗号分隔的值不同的ID

This question already has an answer here:
Multiple rows to one comma-separated value [duplicate] 1 Answer

这个问题已经有了答案:多行到一个逗号分隔的值[duplicate] 1

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  
 2 | 600
 2 | 700

Now when I execute the query in SQL Server, I want table to be like:

现在，当我在SQL Server中执行查询时，我希望表是:

ID | Value
---+-------
 1 | 100,200,300
 2 | 600,700

3 个解决方案

#1

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

#2

Use STUFF method to get your result :

使用STUFF方法获取结果:

CREATE TABLE #table ( ID INT, Value INT)
INSERT INTO #table ( ID , Value )
SELECT  1 , 100 UNION ALL
SELECT   1 , 200 UNION ALL
SELECT  1 , 300   UNION ALL
SELECT  2 , 600 UNION ALL
SELECT  2 , 700

  SELECT ID , STUFF
 (
    (
     SELECT  ', ' + CAST(Value AS VARCHAR)
     FROM #table Inr WHERE inr.ID = Otr.ID FOR XML PATH('')
     ),1,1,'') 
 FROM #table Otr
 GROUP BY ID

#3

    CREATE TABLE #Table1
        ([ID] int, [Value] int)
    ;

    INSERT INTO #Table1
        ([ID], [Value])
    VALUES
        (1, 100),
        (1, 200),
        (1, 300),
        (2, 600),
        (2, 700)



    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,' ') value
from #table1 t
group by id

output

输出

ID  Value
1    100, 200, 300
2    600, 700

#1