在SQL Server中将多行文本连接成单个文本字符串的方法

技术背景

在数据库操作中，有时需要将多行文本连接成单个文本字符串。例如，在处理具有一对多关系的表连接时，需要将相关联的多行记录中的文本合并为一个字符串。不同版本的 SQL Server 提供了不同的方法来实现这一需求。

实现步骤

SQL Server 2005

在 SQL Server 2005 中，可以使用 XML PATH 方法来处理行的连接。以下是一个示例，假设有一个名为 STUDENTS 的表：

-- 创建示例表
DECLARE @STUDENTS TABLE (
    SubjectID INT,
    StudentName NVARCHAR(50)
);

-- 插入示例数据
INSERT INTO @STUDENTS (SubjectID, StudentName)
VALUES
    (1, 'Mary'),
    (1, 'John'),
    (1, 'Sam'),
    (2, 'Alaina'),
    (2, 'Edward');

-- 使用 XML PATH 方法进行连接
SELECT Main.SubjectID,
       LEFT(Main.Students, LEN(Main.Students) - 1) AS "Students"
FROM
    (
        SELECT ST2.SubjectID,
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM @STUDENTS ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]', 'nvarchar(max)') [Students]
        FROM @STUDENTS ST2
        GROUP BY ST2.SubjectID
    ) [Main];

也可以使用更紧凑的方式，使用 stuff 函数跳过第一个逗号：

SELECT ST2.SubjectID, 
    STUFF(
        (
            SELECT ',' + ST1.StudentName AS [text()]
            FROM @STUDENTS ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]', 'nvarchar(max)'), 1, 1, '') [Students]
FROM @STUDENTS ST2
GROUP BY ST2.SubjectID;

使用 COALESCE

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

若行中有 NULL 名称值，会导致结果不正确，可以通过以下两种解决方案修复：

-- 过滤 NULL 值
DECLARE @Names VARCHAR(8000);
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL;

-- 将 NULL 替换为指定值
DECLARE @Names VARCHAR(8000);
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People;

SQL Server 2017+ 和 SQL Azure

可以使用 STRING_AGG 函数：

-- 无分组
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 2016

可以使用 FOR JSON 语法：

-- 假设存在 Person 和 Email 表
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;

PostgreSQL

可以使用数组和字符串转换函数：

-- 创建测试表
CREATE TABLE names (name text);
-- 插入测试数据
INSERT INTO names (name) VALUES ('Peter'), ('Paul'), ('Mary');
-- 聚合为数组
SELECT array_agg(name) from names;
-- 将数组转换为逗号分隔的字符串
SELECT array_to_string(array_agg(name), ', ') from names;

从 PostgreSQL 9.0 开始，也可以使用 string_agg 函数：

select string_agg(name, ',') 
from names;

Oracle 11g Release 2

支持 LISTAGG 函数：

COLUMN employees FORMAT A50;
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

递归 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
OPTION (MAXRECURSION 101);

最佳实践

• 优先使用适合当前 SQL Server 版本的函数，如 SQL Server 2017 及以上版本使用 STRING_AGG 函数，简单高效。
• 处理 NULL 值时，根据业务需求选择过滤 NULL 或替换为指定值。
• 在使用 XML PATH 方法时，注意处理多余的逗号。

常见问题

• NULL 值问题：若行中有 NULL 名称值，使用 COALESCE 时会导致结果不正确，可通过过滤 NULL 或替换 NULL 为指定值来解决。
• 字符串长度限制问题：在使用某些方法时，可能会遇到字符串长度限制，如 Oracle 的 LISTAGG 函数结果字符串超过 4000 字符会抛出异常，需要处理异常或自定义函数。