[SQL Server]SQL Server中如何将一个表中的一列（多行）连接成一行字符串？

问题描述

SQL Server中如何将一个表中的一列（多行）连接成一行字符串？
比如有如下表格及数据（Students）表：

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

我们希望通过SQL查询语句查询到如下的结果：

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

以上的需求如果使用SQL Server语句应该如何实现呢？

方式一、使用 XML PATH

SQL Server 2005+中，可以使用 XML PATH 来实现，如下：

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]

或者：

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

方式二、使用COALESCE

使用COALESCE, SQL Server语句：

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

方式三、使用STUFF

在 SQL Server 2005+ 版本中，可以使用STUFF关键字，如下：

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

方式四、使用 FOR JSON 语法

在 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

方式五、使用 STRING_AGG 关键字

在 SQL Server 2017 版本中，可以使用 STRING_AGG 关键字，如下：

1.不分组的情况：

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

2.分组的情况：

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

3.分组并按子串排序的情况：

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