logo标签列表

如何在SQL Server中将多行文本连接成单个文本字符串

sqlsql-servercsvstring-concatenationgroup-concat
2398

考虑一个包含姓名的数据库表格，有三行：

Peter
Paul
Mary

有没有简单的方法将这些内容转换成一个字符串：Peter, Paul, Mary？
29
如需针对SQL Server的特定答案，请尝试此问题。 - Matt Hamilton
22
对于MySQL，请查看此答案中的Group_Concat。它可以将多行合并为一个字段。 - Pykler
31
希望SQL Server的下一个版本能够添加一项新功能，以优雅地解决多行字符串连接的问题，而不需要使用 FOR XML PATH 这种繁琐方式。 - Pete Alvin
4
不是SQL，但如果这只是一次性的事情，你可以将列表粘贴到这个在线工具convert.town/column-to-comma-separated-list中进行转换。 - Stack Man
4
在Oracle数据库中，你可以在11g r2版本之后使用LISTAGG(COLUMN_NAME)函数，它可以实现与旧版中不被支持的WM_CONCAT(COLUMN_NAME)函数相同的功能。 - Richard
显示剩余5条评论
48个回答
1648
如果您使用的是SQL Server 2017或Azure，请参阅Mathieu Renda answer。
当我尝试连接具有一对多关系的两个表时，我遇到了类似的问题。在SQL 2005中，我发现XML PATH方法可以轻松处理行的串联。
如果有一个名为STUDENTS的表：
SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

我期望的结果是：

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

我使用了以下的 T-SQL：
SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

如果您可以连接逗号并使用substring跳过第一个逗号，那么您可以以更紧凑的方式完成相同的操作，因此无需进行子查询：

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
18
很棒的解决方案。如果您需要处理 HTML 中的特殊字符，则以下内容可能会有所帮助：Rob Farley: 使用 FOR XML PATH('') 处理特殊字符。 - user140628
14
显然，如果名称包含XML字符，例如<或&，则此方法无法正常工作。请参见@BenHinman的评论。 - Sam
30
注：此方法依赖于未记录的 FOR XML PATH ('') 行为，这意味着它不应被视为可靠的，因为任何补丁或更新都可能改变其功能方式。基本上，这是依赖于已弃用的功能。 - Bacon Bits
34
“FOR XML”旨在生成XML，而非串联任意字符串。这就是为什么它将“&”，“<”和“>”转义为XML实体代码的原因。我认为它还会在属性中将“"”和“'”转义为“"”和“'”。即使你可以让它做类似于“GROUP_CONCAT()”，“string_agg()”，“array_agg()”，“listagg()”等的事情，它也不是那些函数。我们应该花时间要求Microsoft实现一个适当的函数。 - Bacon Bits
27
好消息：MS SQL Server 将在下一个版本中添加 string_agg 函数，这样一切都可以解决了。 - Jason C
显示剩余16条评论
1148

使用 COALESCE：

这个答案可能会返回意外的结果。为了获得一致的结果，请使用其他答案中详细介绍的 FOR XML PATH 方法之一。

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

只是一些解释（因为这个答案似乎经常被查看）：

  • Coalesce实际上只是一个有用的技巧，可以完成两件事情：

1）不需要使用空字符串值初始化@Names。

2）不需要在结尾处再次删除额外的分隔符。

  • 如果一行具有 NULL Name值，则上述解决方案将提供不正确的结果（如果存在 NULL ，则 NULL 将使@Names 在此之后成为 NULL ，下一行将重新开始作为空字符串。有两种解决方法： 
DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

或者：

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

根据您想要的行为（第一种选择只是过滤掉NULL，第二种选择将它们保留在列表中，并用标记消息进行标记[将“N/A”替换为适合您的内容]）。

84
明确一点，coalesce与创建列表无关，它只是确保不包含NULL值。 - Graeme Perrow
23
@Graeme Perrow它不排除NULL值（需要WHERE来排除--如果输入值之一为NULL，这将丢失结果），而且在这种方法中是必需的，因为：NULL +非NULL-> NULL和非NULL + NULL-> NULL; 此外，默认情况下@Name为NULL，并且实际上该属性用作隐式哨兵，以确定是否应添加“，” - user166390
72
请注意，此连接方法依赖于SQL Server使用特定计划执行查询。我曾在使用此方法时（附加了ORDER BY）遇到过问题。当处理少量行时，它可以正常工作，但是对于更多数据，SQL Server会选择不同的计划，导致选择第一个项目而没有进行任何连接。请参阅Anith Sen的这篇文章。 - fbarber
20
由于使用了T-SQL变量，此方法不能用作选择列表或where子句中的子查询。在这种情况下，您可以使用@Ritesh提供的方法。 - R. Schreurs
17
这不是一种可靠的连接方法，它不受支持，不应该使用（根据微软公司，例如https://support.microsoft.com/en-us/kb/287515，https://connect.microsoft.com/SQLServer/Feedback/Details/704389）。 它可能会在没有警告的情况下发生更改。使用在https://dev59.com/Q2445IYBdhLWcg3wH2rH#5031297中讨论的XML PATH技术。我在这里写得更多：http://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/ - Marc Durdin
显示剩余16条评论
887

SQL Server 2017+和SQL Azure: STRING_AGG

从下一个版本的SQL Server开始，我们终于可以跨行连接字符串而不必使用任何变量或XML技巧。

STRING_AGG（Transact-SQL）

无需分组

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

分组：

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

使用分组和子排序

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
5
与CLR解决方案不同，您可以控制排序方式。 - canon
1
有没有办法在没有GROUP BY的情况下进行排序（例如“无分组”示例）？ - RuudvK
1
更新：我已经完成了以下操作，但是否有更简洁的方法？SELECT STRING_AGG(Name, ', ') AS Departments FROM ( SELECT TOP 100000 Name FROM HumanResources.Department ORDER BY Name) D; - RuudvK
2
我不得不将它转换为NVarchar(max)才能使其工作。SELECT STRING_AGG(CAST(EmpName as NVARCHAR(MAX)), ',') FROM EmpTable as t - Varun
2
我一生都错过了STRING_AGG。这应该是被接受的答案！ - Yahya
显示剩余5条评论
421

在SQL Server中，还没有展示过通过XMLdata()命令的一种方法是：

假设有一个名为NameList的表，其中有一个名为FName的列，

SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')

返回：

"Peter, Paul, Mary, "

只需要处理多余的逗号即可。

从@NReilingh的评论中采用以下方法，可以删除尾随的逗号。假设使用相同的表和列名：

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
17
哇塞，太神奇了！像你的示例一样单独执行时，结果会被格式化为超链接，点击后（在SSMS中）会打开一个包含数据的新窗口，但是当作为更大查询的一部分时，它只是出现为字符串。它是字符串还是XML需要我在使用这些数据的应用程序中以不同方式处理？ - Ben
10
这种方法还可以将像 < 和 > 这样的字符进行 XML 转义。因此，选择 '<b>' + FName + '</b>' 的结果将变成"<b>John</b><b>Paul..."。 - Lukáš Lánský
8
整洁的解决方案。我注意到即使我没有添加“+ ', '”，它仍然在每个连接元素之间添加一个空格。 - Baodad
9
@Baodad，这似乎是交易的一部分。您可以通过添加一个特殊字符进行变通。例如，对于任何长度的完美逗号分隔列表，请使用以下查询语句：SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'') - NReilingh
1
哇，实际上在我的测试中使用data()和replace比不使用要快得多。非常奇怪。 - NReilingh
显示剩余6条评论
355

在SQL Server 2005中

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

在 SQL Server 2016 中

你可以使用 FOR JSON 语法

例如：

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

结果将变为

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

即使您的数据包含无效的XML字符，这也可以正常工作。

'"},{"_":"'是安全的，因为如果您的数据包含'"},{"_":"',它将被转义为"},{\"_\":\"

您可以使用任何字符串分隔符替换', '

在SQL Server 2017和Azure SQL数据库中

您可以使用新的STRING_AGG函数

4
善用STUFF函数以消除前两个字符。 - David
4
我最喜欢这个解决方案，因为我可以很容易地在选择列表中使用它，只需添加“作为<label>”。我不确定如何使用@Ritesh的解决方案来实现这一点。 - R. Schreurs
18
这个选项比被接受的答案更好，因为它还可以处理未转义的 XML 保留字符，例如 <、>、& 等，而 FOR XML PATH('') 将自动进行转义。 - BateTech
163
在MySQL中，有一个函数GROUP_CONCAT()，允许你将多个行的值连接起来。例如： 

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a
基本上可以工作。需要考虑两件事情：1）如果您的列不是CHAR类型，您需要进行转换，例如通过GROUP_CONCAT(CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',')进行转换；2）如果有许多值传入，您应该根据https://dev59.com/_nM_5IYBdhLWcg3wq1GF#1278210中的说明增加`group_concat_max_len`。 - hardmooth
这个方法在2022年3月对我有效。我有一些包含URL的行，想要将它们合并到单独的一列中，这个方法很好用。谢谢！ - Wilfred Almeida
3
OP的问题涉及[MS] SQL Server。 - GoldBishop
73

使用 COALESCE 函数 - 了解详情

举个例子：

102

103

104

然后在 SQL Server 中编写以下代码：

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

输出结果将是：

102,103,104
4
我认为这是最好的解决方案，因为它避免了使用FOR XML时出现的编码问题。我使用了“Declare @Numbers AS Nvarchar(MAX)”并且它可以正常工作。请问您能否解释一下为什么您建议不使用它？ - EvilDr
12
这个解决方案已经在8年前发布了！https://dev59.com/i3VC5IYBdhLWcg3wvT_g#194887 - Andre Figueiredo
为什么这个查询返回的是???符号而不是西里尔字母？这只是输出问题吗？ - Akmal Salikhov
@EvilDr 你可以避免使用XML编码。请参考：https://dev59.com/FWUo5IYBdhLWcg3w9TTb - Developer Webs
为什么不使用问题中的示例？ - Peter Mortensen
67

PostgreSQL的数组非常棒。例如：

创建一些测试数据：

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
 name
-------
 Peter
 Paul
 Mary
(3 rows)

将它们聚合到一个数组中：

test=# select array_agg(name) from names;
 array_agg
-------------------
 {Peter,Paul,Mary}
(1 row)

将数组转换为逗号分隔的字符串:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

自从PostgreSQL 9.0版本以来，更容易了。引用“没有名字的马”的删除答案：

select string_agg(name, ',') 
from names;
2
如果你需要超过一个列，例如在括号中他们的员工ID，请使用concat运算符：选择array_to_string(array_agg(name ||'('||id||')' - ProbablePrime
4
仅适用于 [tag:mysql]，不适用于 [tag:sql-server]。 - GoldBishop
50

Oracle 11g Release 2支持LISTAGG函数。文档在这里。

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

警告

如果存在结果字符串超过4000个字符的可能，请小心实现此函数。它会抛出异常。如果是这种情况，则需要处理异常或编写自己的函数，以防止连接的字符串超过4000个字符。

2
对于旧版本的Oracle，wm_concat是完美的选择。它的使用在Alex提供的链接中有解释。谢谢Alex！ - toscanelli
1
LISTAGG 运行完美！只需阅读此处链接的文档。wm_concat 已从 12c 版本开始删除。 - asgs
39
在SQL Server 2005及更高版本中，使用以下查询来连接行。
DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t
2
我认为当值包含XML符号，如<或&时，这会失败。 - Sam
在提供的示例中运行得非常好。我使用了CTE而不是临时表或变量。https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 - Stritof
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接