如何在 SQL Server 中将多行文本合并为单个文本字符串?

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

Peter
Paul
Mary

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

答案

如果您使用的是 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

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

使用COALESCE

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

只是一些解释(因为此答案似乎获得了相对常规的意见):

  • 实际上,Coalcece 只是一个有用的作弊手段,可以完成两件事:

1)无需使用空字符串值初始化@Names

2)无需在末端剥离额外的分隔器。

  • 如果行的名称值为NULL (如果存在NULL ,则该解决方案将给出错误的结果(如果存在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' 替换为适合您的内容))。

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;

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

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

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 函数

在 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

使用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

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)

完成

从 PostgreSQL 9.0 开始,它变得更加容易

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 个字符。

在 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