SQL多行列字符串拼接问题：按员工分组聚合不同Type的SubType

问题描述 #

现有表e的数据如下：

eId FName LName Type SubType
1   a     aa    1    S11a
1   a     aa    1    S12a
1   a     aa    1    S13a
1   a     aa    3    S31a
1   a     aa    3    S32a
2   b     bb    1    S11b
2   b     bb    1    S12b
2   b     bb    3    S31b
2   b     bb    3    S32b

期望按员工（eId、FName、LName）分组，将Type=1的SubType拼接成SubType1，Type=3的SubType拼接成SubType2，得到结果：

eId FName LName SubType1          SubType2
1   a     aa    S11a;S12a;S13a    S31a;S32a
2   b     bb    S11b;S12b         S31b;S32b

你尝试用STUFF函数实现但未成功，返回的SubType列值为Null，现有查询语句如下：

SELECT e1.eId, e1.FName, e1.LName 
, STUFF(( SELECT N'; ' + [SubType] FROM e e2 WHERE e1.eId = e2.eId AND e1.FName = e2.FName AND e1.LName = e2.LName AND e1.[Type] = e2.[Type] AND e1.[Type] = 1 FOR XML PATH ('')), 1, 2, '') AS SubType1 
, STUFF(( SELECT N'; ' + [SubType] FROM e e2 WHERE e1.eId = e2.eId AND e1.FName = e2.FName AND e1.LName = e2.LName AND e1.[Type] = e2.[Type] AND e1.[Type] = 3 FOR XML PATH ('')), 1, 2, '') AS SubType2 
FROM e e1 
GROUP BY e1.eId, e1.FirstName, e1.LastName, e1.[Type]

现有查询的问题分析 #

我帮你梳理下几个关键错误点：

• 分组字段错误：GROUP BY里包含了e1.[Type]，这会把同一个员工的记录按Type拆分成两组（Type=1和Type=3），导致最终结果出现重复的员工行，而且每组只能拿到对应Type的拼接值，另一列自然为NULL。同时你还把原表的FName/LName写成了FirstName/LastName，这会引发字段不存在的错误或者分组逻辑混乱。
• 子查询条件逻辑错误：子查询里同时加了e1.[Type] = e2.[Type]和e1.[Type] = 1（或3），因为分组后e1.Type要么是1要么是3，所以当处理Type=1的分组时，SubType2的子查询中e1.[Type] =3永远不成立，返回空字符串，经过STUFF处理后就是NULL；同理Type=3的分组中SubType1是NULL。

正确的SQL实现方案 #

方案一：兼容旧版本SQL Server（用STUFF+FOR XML PATH） #

去掉GROUP BY里的Type字段，同时子查询直接过滤对应Type，不需要关联主查询的Type：

SELECT 
    e1.eId, 
    e1.FName, 
    e1.LName,
    -- 拼接Type=1的SubType
    STUFF((
        SELECT N';' + [SubType] 
        FROM e e2 
        WHERE e2.eId = e1.eId 
          AND e2.FName = e1.FName 
          AND e2.LName = e1.LName 
          AND e2.[Type] = 1
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SubType1,
    -- 拼接Type=3的SubType
    STUFF((
        SELECT N';' + [SubType] 
        FROM e e2 
        WHERE e2.eId = e1.eId 
          AND e2.FName = e1.FName 
          AND e2.LName = e1.LName 
          AND e2.[Type] = 3
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SubType2
FROM e e1
GROUP BY e1.eId, e1.FName, e1.LName;

这里做了几个优化：

• 用TYPE和.value('.', 'NVARCHAR(MAX)')避免特殊字符被转义（比如&变成&）
• 拼接时用;开头，STUFF去掉第一个分号，比用; 更简洁

方案二：SQL Server 2017及以上版本（用STRING_AGG） #

如果你的SQL Server版本支持STRING_AGG函数，写法会更简洁直观：

SELECT 
    eId, 
    FName, 
    LName,
    STRING_AGG(CASE WHEN [Type] =1 THEN SubType END, ';') AS SubType1,
    STRING_AGG(CASE WHEN [Type] =3 THEN SubType END, ';') AS SubType2
FROM e
GROUP BY eId, FName, LName;

STRING_AGG会自动忽略NULL值，所以CASE语句中不符合条件的SubType会变成NULL，不会被拼接进去，完美满足需求。

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