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


考虑一个包含三个行的名称的数据库表:

Peter
Paul
Mary

有没有一种简单的方法可以将其转换为单个字符串 Peter, Paul, Mary


1489
2017-10-11 23:49


起源


有关SQL Server特定的答案,请尝试 这个问题。 - Matt Hamilton
对于MySQL,请查看 GROUP_CONCAT 从 这个答案 - Pykler
我希望下一版本的SQL Server能够提供一个新功能来优雅地解决多行字符串连接,而不需要FOR XML PATH的愚蠢。 - Pete Alvin
一步一步的教程描述上述答案:试试这篇文章:[ sqlmatters.com/Articles/... ] - saber tabatabaee yazdi
不是SQL,但如果这是一次性的事情,您可以将列表粘贴到此浏览器工具中 convert.town/column-to-comma-separated-list - Stack Man


答案:


如果您使用的是SQL Server 2017或Azure,请参阅 Mathieu Renda回答

当我尝试使用一对多关系加入两个表时,我遇到了类似的问题。在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 ('')
            ) [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 ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

1110
2018-02-13 11:53



好的解决方案如果您需要处理HTML中的特殊字符,以下内容可能会有所帮助: Rob Farley:使用FOR XML PATH处理特殊字符('')。
如果名称包含XML字符,显然这不起作用 < 要么 &。请参阅@ BenHinman的评论。 - Sam
注意:这种方法依赖于无证件的行为 FOR XML PATH ('')。这意味着它不应被视为可靠,因为任何补丁或更新都可能改变其功能。它基本上依赖于已弃用的功能。 - Bacon Bits
@Whelkaholism最重要的是 FOR XML 旨在生成XML,而不是连接任意字符串。这就是它逃脱的原因 &, < 和 > 到XML实体代码(&amp;, &lt;, &gt;)。我认为它也会逃脱 " 和 ' 至 &quot; 和 &apos; 在属性中也是如此。它的 不  GROUP_CONCAT(), string_agg(), array_agg(), listagg()等等,即使你可以做到这一点。我们 应该 花时间要求微软实施适当的功能。 - Bacon Bits
好消息: MS SQL Server将添加 string_agg 在v.Next。 所有这一切都可以消失。 - Jason C


当存在ORDER BY子句时,此答案可能会返回意外结果。要获得一致的结果,请使用其他答案中详述的FOR XML PATH方法之一。

使用 COALESCE

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

只是一些解释(因为这个答案似乎得到相对规律的观点):

  • Coalesce真的只是一个有用的骗子,可以完成两件事:

1)无需初始化 @Names 带有空字符串值。

2)最后无需剥去额外的分离器。

  • 如果一行有一个,上面的解决方案将给出不正确的结果 空值 名称值(如果有 空值空值 会做的 @Names  空值 在该行之后,下一行将再次作为空字符串重新开始。使用以下两种解决方案之一轻松修复:
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

取决于您想要的行为(第一个选项只是过滤 空值s out,第二个选项使用标记消息将它们保留在列表中[用适合您的任何内容替换'N / A'])。


895
2017-10-12 00:18



要清楚,coalesce与创建列表无关,它只是确保不包含NULL值。 - Graeme Perrow
@Graeme Perrow它不排除NULL值(需要WHERE - 这将是 失去结果 如果其中一个输入值是NULL),那么 这种方法是必需的 因为:NULL +非NULL - > NULL和非NULL + NULL - > NULL;默认情况下,@ Name也是NULL,实际上,该属性在此处用作隐式标记,以确定是否应添加“,”。
请注意,这种连接方法依赖于SQL Server使用特定计划执行查询。我被抓住了使用这种方法(添加了ORDER BY)。当处理少量行时,它工作正常,但是有了更多数据,SQL Server选择了一个不同的计划,导致选择第一个没有连接的项目。看到 本文 作者:Anith Sen. - fbarber
此方法不能用作选择列表或where子句中的子查询,因为它使用tSQL变量。在这种情况下,您可以使用@Ritesh提供的方法 - R. Schreurs
这不是一种可靠的连接方法。它不受支持,不应使用(根据Microsoft,例如, support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389)。它可以在没有任何警告的情使用中讨论的XML PATH技术 stackoverflow.com/questions/5031204/... 我在这里写了更多: marc.durdin.net/2015/07/... - Marc Durdin


一种尚未通过该方法显示的方法 XML  data() MS SQL Server中的命令是:

假设名为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

297
2018-04-05 21:19



圣洁的,太棒了!当单独执行时,如在您的示例中,结果被格式化为超链接,单击时(在SSMS中)打开一个包含数据的新窗口,但当作为较大查询的一部分使用时,它只显示为字符串。它是一个字符串?或者是我需要在将使用此数据的应用程序中区别对待的xml? - Ben
这种方法也可以XML转义像<和>这样的字符。因此,选择'<b>'+ FName +'</ b>'会产生“&lt; b&gt; John&lt; / b&gt;&lt; b&gt; Paul ......” - Lukáš Lánský
整洁的解决方案。我注意到即使我不添加 + ', ' 它仍然在每个连接元素之间添加一个空格。 - Baodad
@Baodad这似乎是交易的一部分。您可以通过替换添加的令牌字符来解决此问题。例如,这为任何长度都有一个完美的逗号分隔列表: SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'') - NReilingh
哇,实际上在我的测试中使用data()和替换是更好的表现。超级怪异。 - NReilingh


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功能


215
2018-03-14 05:00



很好地利用STUFF函数来修改前两个字符。 - David
我最喜欢这个解决方案,因为我可以通过附加'as <label>'轻松地在选择列表中使用它。我不知道如何使用@Ritesh解决方案。 - R. Schreurs
这比接受的答案更好,因为此选项还处理未转义的XML保留字符,例如 <, >, &等等 FOR XML PATH('') 会自动逃脱。 - BateTech
这是一个很棒的响应,因为它解决了这个问题并提供了在不同版本的SQL中做事的最佳方法,现在我希望我可以使用2017 / Azure - Chris Ward


SQL Server 2017+和SQL Azure:STRING_AGG

从下一版本的SQL Server开始,我们最终可以跨行连接,而不必诉诸任何变量或XML witchery。

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;

168
2017-10-12 00:10



而且,与CLR解决方案不同,您可以控制排序。 - canon
这适用于SQL Azure。很棒的答案! - user2721607
这在Azure SQL中也适用于我。辉煌! - Kevin Stone


在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

98
2018-04-05 07:08



曾经喜欢这个,还没有看到任何其他Db的这个功能的替代品! - Binoj Antony
这完全解决了我的问题。我试图将一个帐户的给定费用的所有付款日期拉出来,这完全解决了它。谢谢! - Maximus
效果很好。但是当我使用时 SEPARATOR '", "' 我会在最后一个条目的末尾错过一些字符。这为什么会发生? - gooleem
@gooleem我不清楚你的意思,但是这个函数只在项之间放置分隔符,而不是之后。如果那不是答案,我建议发一个新问题。 - Darryl Hein
@DarrylHein根据我的需要,我使用了上面的分隔符。但是这会在输出结束时削减一些字符。这很奇怪,似乎是一个错误。我没有解决方案,我只是解决了。 - gooleem


使用 合并  - 从这里了解更多信息

举个例子:

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

52
2018-03-08 16:29



这实际上是IMO的最佳解决方案,因为它避免了FOR XML带来的编码问题。我用了 Declare @Numbers AS Nvarchar(MAX) 它工作得很好。你能解释为什么建议你不要使用它吗? - EvilDr
此解决方案已于8年前发布! stackoverflow.com/a/194887/986862 - Andre Figueiredo
为什么这个查询返回???符号而不是西里尔语?这只是输出问题吗? - Akmal Salikhov


Oracle 11g第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个字符。


42
2017-08-09 21:20



对于旧版本的Oracle,wm_concat是完美的。它的用途在Alex的链接礼物中有所解释。亚历克斯! - toscanelli
LISTAGG 工作完美!只需阅读此处链接的文档即可。 wm_concat 从版本12c开始删除。 - asgs


Postgres数组非常棒。例:

创建一些测试数据:

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)

DONE

自从PostgreSQL 9.0以来 更容易


41
2017-07-06 12:46



如果您需要多个列,例如括号中的员工ID,请使用concat运算符: select array_to_string(array_agg(name||'('||id||')' - Richard Fox
不适用于 SQL服务器,只有 MySQL的 - GoldBishop