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

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

---

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

使用 COALESCE：

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']）。

---

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;

---

通过 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 syntax

IE

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 function

---

在 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 - Learn more from here

例如：

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

---

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;

---

Oracle 11g 第 2 版支持 LISTAGG 函数。文档 here。

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

---

建议使用递归 CTE 解决方案，但未提供代码。下面的代码是递归 CTE 的示例。

请注意，尽管结果与问题匹配，但数据与给定的描述并不完全匹配，因为我假设您确实希望对行组执行此操作，而不是表中的所有行。将其更改为匹配表中的所有行留给读者作为练习。

;WITH basetable AS (
    SELECT
        id,
        CAST(name AS VARCHAR(MAX)) name,
        ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
        COUNT(*) OVER (Partition BY id) recs
    FROM (VALUES
        (1, 'Johnny', 1),
        (1, 'M', 2),
        (2, 'Bill', 1),
        (2, 'S.', 4),
        (2, 'Preston', 5),
        (2, 'Esq.', 6),
        (3, 'Ted', 1),
        (3, 'Theodore', 2),
        (3, 'Logan', 3),
        (4, 'Peter', 1),
        (4, 'Paul', 2),
        (4, 'Mary', 3)
    ) g (id, name, seq)
),
rCTE AS (
    SELECT recs, id, name, rw
    FROM basetable
    WHERE rw = 1

    UNION ALL

    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
    FROM basetable b
    INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4

---