使用逗号作为分隔符，将多行数据合并为一行。

Question

如果我执行 SELECT username FROM Users，我会得到如下结果:

username
--------
Paul
John
Mary

但是我真正需要的是一行，将所有值用逗号分隔开，就像这样：

Paul, John, Mary

如何实现呢？

- Pavel Bastov

Answer 1

 select
   distinct  
    stuff((
        select ',' + u.username
        from users u
        where u.username = username
        order by u.username
        for xml path('')
    ),1,1,'') as userlist
from users
group by username

之前有个错别字，上面的代码可以正常工作

- Hogan

---

你是一个天才。此外，这将在列表前面加入一个空格。将STUFF选项设置为1、2，即可去掉该空格。现在我需要搞清楚这是怎么工作的。 - Jared

免责声明：此方法不适用于包含特殊字符如<或&的数据。 - keeehlan

@BoratSagdiyev - kehrk 是错误的，FOR XML 会自动编码，请查看 http://sqlfiddle.com/#!6/b824a/1。 - Hogan

@kehrk - 嗯...不需要。运行良好。http://sqlfiddle.com/#!6/b824a/1 - Hogan

应该被接受为正确答案。 - user9168386

Answer 2

这应该适用于您。已经测试过了，可以追溯到 SQL 2000。

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

- mwigdahl

---

+1，但是select SUBSTRING(@tmp, 0, LEN(@tmp))看起来不正确（对我来说），但显然是可以运行的（我试过了）。MSDN页面中关于substring的晦涩难懂的语言未能说明为什么它会工作，但我猜想它的结束点是start_expression + length_expression而没有更正start_expression，所以如果你用一个小于1的数字开始，它将从“第一个字符”（例如1）开始，我想它算是通过后门实现了。虽然如此，我还是打算使用select SUBSTRING(@tmp, 1, LEN(@tmp) - 1)。 - T.J. Crowder

是的，显然，由于select SUBSTRING('testing', -2, 5)会给出我们'te'（例如，正是select SUBSTRING('testing', 1, 2)所给出的），因为在两种情况下得到的（独占式）结束索引都是3。这不是我想要依赖的行为。你有什么具体原因吗？ - T.J. Crowder

@T.J.Crowder - 实际上，因为目标是删除最后一个字符，我认为stuff(str,len(str)-1,1,'')更快。 - Hogan

@BoratSagdiyev -- 你说得对，那正是它所做的。这种语法实际上扩展成了一个迷你光标。一般来说，Hogan在下面提供的FOR XML PATH解决方案对于大型情况更快，如果您不关心特殊字符的XML转义。 - mwigdahl

SQL Server 2017/ SQL Azure支持STRING_AGG函数，用于以下目的- https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql https://dev59.com/i3VC5IYBdhLWcg3wvT_g#42778050 - Chirag Rupani

Answer 3

多种方法的良好评论：

http://blogs.msmvps.com/robfarley/2007/04/07/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql/

文章副本 -

在T-SQL中，Coalesce不是字符串连接的答案。多年来，我看到了许多关于使用COALESCE函数在T-SQL中实现字符串连接的帖子。这是其中一个例子（借鉴自Readifarian Marc Ridey）。

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory

SELECT @categories

这个查询可能非常有效，但需要小心处理，并且必须正确理解使用COALESCE的方法。COALESCE是ISNULL的版本，可以接受多个参数。它返回参数列表中第一个不为null的值。所以实际上它与字符串连接没有任何关系，下面的代码片段完全相同 - 而不使用COALESCE：

DECLARE @categories varchar(200)
SET @categories = ''

SELECT @categories = @categories + ',' + Name
FROM Production.ProductCategory

SELECT @categories

然而，数据库的无序性使得这种方法不可靠。T-SQL目前没有串联函数的主要原因是这是一个需要考虑元素顺序的聚合操作。使用变量赋值的字符串拼接方法时，你可能会发现返回的结果并不包含所有的值，特别是当你希望将子字符串按照特定顺序排列时。考虑以下示例，在我的机器上只返回了',Accessories'，但我希望它返回',Bikes,Clothing,Components,Accessories':

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ',','') + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)

SELECT @categories

更好的方法是使用一种考虑顺序的方法，并且这种方法已经被SQL2005专门用于字符串拼接 - FOR XML PATH('')。

SELECT ',' + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH('') 

最近我发布了一篇关于在使用子查询时比较GROUP BY和DISTINCT的文章，在里面展示了使用FOR XML PATH('')的方法。看一下这篇文章，你就会知道它在子查询中是如何工作的。'STUFF'函数只是为了去掉前导逗号。

USE tempdb;
GO
CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
INSERT t1 values (1,'Jamie');
INSERT t1 values (1,'Joe');
INSERT t1 values (1,'John');
INSERT t1 values (2,'Sai');
INSERT t1 values (2,'Sam');
GO

select
    id,
    stuff((
        select ',' + t.[name]
        from t1 t
        where t.id = t1.id
        order by t.[name]
        for xml path('')
    ),1,1,'') as name_csv
from t1
group by id
; 

在子查询中，FOR XML PATH是唯一可以使用ORDER BY的情况之一。另一个是TOP。当您使用未命名列和FOR XML PATH('')时，您将获得直接的连接，没有XML标记。这意味着字符串将被HTML编码，因此，如果您正在连接可能包含<字符（等）的字符串，则您应该在此之后进行修复，但无论如何，这仍然是SQL Server 2005中连接字符串的最佳方法。

- A-K

---

Alex，我将整篇文章都添加了进来，以防原始链接失效。希望你能接受这些更改。谢谢。陈齐。 - Erran Morad

@AlexKuznetsov - FYI - 这篇文章已经超过7年了，其中包含的信息自SQL 2008发布以来就不再正确。 - Hogan

你在使用FOR XML('')版本方面做得很好，我一直在努力实现按ID分区的聚合行。你的示例不仅解决了这个问题，而且我也明白了它的工作原理。 - Morvael

有没有办法在最后一个逗号分隔的项目中添加“和”？ - Irish Redneck

有趣！当我询问分隔符时，数据库管理员一直告诉我“别管它”。看来我最初误解了他们的意思。现在我得去道歉了... - FloverOwe

Answer 4

在 mwigdahls 的回答基础上进行补充。如果您还需要进行分组，以下是让它看起来像的方法：

group, csv
'group1', 'paul, john'
'group2', 'mary'

    --drop table #user
create table #user (groupName varchar(25), username varchar(25))

insert into #user (groupname, username) values ('apostles', 'Paul')
insert into #user (groupname, username) values ('apostles', 'John')
insert into #user (groupname, username) values ('family','Mary')


select
    g1.groupname
    , stuff((
        select ', ' + g.username
        from #user g        
        where g.groupName = g1.groupname        
        order by g.username
        for xml path('')
    ),1,2,'') as name_csv
from #user g1
group by g1.groupname

- Tom McDonald

Answer 5

您可以使用此查询来完成上述任务：

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2 = @test 

详情和逐步说明，请访问以下链接：http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html

该链接介绍了如何将SQL Server表列数据转换为XML格式，并提供了详细的步骤说明。

- Rashmi Kant

Answer 6

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

source: http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string

- vinay

Answer 7

你可以使用stuff()将行转换为逗号分隔的值。

select
EmployeeID,
stuff((
  SELECT ',' + FPProjectMaster.GroupName 
      FROM     FPProjectInfo AS t INNER JOIN
              FPProjectMaster ON t.ProjectID = FPProjectMaster.ProjectID
      WHERE  (t.EmployeeID = FPProjectInfo.EmployeeID)
              And t.STatusID = 1
              ORDER BY t.ProjectID
       for xml path('')
       ),1,1,'') as name_csv
from FPProjectInfo
group by EmployeeID;

感谢 @AlexKuznetsov 提供的参考，以得出这个答案。

- Ram

Answer 8

在SQLite中，这更加简单。我认为MySQL、MSSql和Orable都有类似的实现。

CREATE TABLE Beatles (id integer, name string );
INSERT INTO Beatles VALUES (1, "Paul");
INSERT INTO Beatles VALUES (2, "John");
INSERT INTO Beatles VALUES (3, "Ringo");
INSERT INTO Beatles VALUES (4, "George");
SELECT GROUP_CONCAT(name, ',') FROM Beatles;

- elcuco

---

问题被标记为 tsql，即 MS SQL Server。在 SQL Server 中不存在 GROUP_CONCAT。然而，自 SQL Server 2017 起，有一个名为 STRING_AGG 的函数，它具有类似的功能。 - Simon Elms

Answer 9

在MS SQL Server 2005/2008中，一个干净灵活的解决方案是创建一个CLR聚合函数。

你可以在谷歌上找到一些文章（含代码）。

这篇文章看起来通过使用C#全程指导了整个过程。

- Arjan Einbu

Answer 10

如果你是通过PHP执行这个，那么这个怎么样？

$hQuery = mysql_query("SELECT * FROM users");
while($hRow = mysql_fetch_array($hQuery)) {
    $hOut .= $hRow['username'] . ", ";
}
$hOut = substr($hOut, 0, strlen($hOut) - 1);
echo $hOut;

- James Brooks

---

糟糕了，看起来你正在通过控制台运行这个程序。 - James Brooks

我需要用SQL完成，不要用PHP或其他什么语言（实际上我在使用C#）。 - Pavel Bastov

是的，我注意到它不是 PHP。 - James Brooks