如何将多行中的文本连接成 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

当存在 ORDER BY 子句时，此答案可能会返回意外结果。要获得一致的结果，请使用其他答案中详述的 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 s，第二个选项将它们保留在列表中，并带有标记消息 [用适合您的任何内容替换'N / A']）。

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