在SQL Server中实现跨表查询结果单列逗号分隔展示

场景说明 #

现有两张表：

• faculty表：存储教师信息，其中subjects列以逗号分隔存储科目编码
• subject表：存储科目编码与对应名称的映射关系

需要将faculty每行的科目编码转换为对应的名称，并合并成单个逗号分隔的列输出。

示例表数据 #

faculty表 #

subject表 #

期望输出 #

解决方案 #

方法1：SQL Server 2017及以上版本（推荐） #

利用STRING_SPLIT拆分逗号分隔的编码，再用STRING_AGG合并名称：

SELECT 
    f.faculty_id,
    f.name,
    STRING_AGG(s.subject_name, ',') AS subject_names
FROM faculty f
CROSS APPLY STRING_SPLIT(f.subjects, ',') split
JOIN subject s ON TRIM(split.value) = s.subject_id
-- 处理空编码的情况
WHERE split.value IS NOT NULL AND TRIM(split.value) != ''
GROUP BY f.faculty_id, f.name;

说明：

• CROSS APPLY STRING_SPLIT将每行的subjects拆分为独立行
• TRIM用于清理编码中可能存在的空格
• STRING_AGG按教师分组，将关联到的科目名称合并为逗号分隔字符串

方法2：SQL Server 2016及以下版本 #

由于低版本不支持STRING_SPLIT和STRING_AGG，采用XML拼接的方式实现：

SELECT 
    f.faculty_id,
    f.name,
    STUFF((
        SELECT ',' + s.subject_name
        FROM (
            -- 用XML模拟拆分逗号分隔的编码
            SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS subject_id
            FROM (
                SELECT CAST('<X>' + REPLACE(f.subjects, ',', '</X><X>') + '</X>' AS XML) AS x
            ) t
            CROSS APPLY x.nodes('/X') m(n)
        ) split
        JOIN subject s ON split.subject_id = s.subject_id
        WHERE split.subject_id IS NOT NULL AND split.subject_id != ''
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS subject_names
FROM faculty f
GROUP BY f.faculty_id, f.name;

说明：

• 通过XML节点拆分编码字符串
• FOR XML PATH('')将科目名称拼接成带逗号的字符串
• STUFF去掉拼接结果开头多余的逗号

注意事项 #

• 如果科目编码是数字类型，需将拆分后的字符串转换为对应类型（如CAST(split.value AS INT)）再关联
• 确保subject表中subject_id是唯一值，避免重复输出科目名称
• 若faculty表的subjects列存在NULL，可通过ISNULL(f.subjects, '')处理，避免报错

内容的提问来源于stack exchange，提问作者sapter