使用FOR XML PATH按分组合并多行值到单个单元格的问题排查

原始数据表 #

期望输出 #

按Reference和UserName分组，将每组对应的OrderId合并为逗号分隔的Orders字段，结果如下：

错误的SQL及输出 #

执行以下SQL后，同一Reference下不同用户的OrderId被错误合并：

SELECT
    tbl2.Reference, tbl2.UserName,
    STUFF((SELECT ',' + CAST(tbl1.OrderId AS varchar (30))
           FROM Table tbl1
           WHERE tbl1.Reference = tbl2.Reference
           FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Orders
FROM
    Table tbl2
GROUP BY
    tbl2.UserName, tbl2.Reference 

错误输出：

修正后的SQL #

错误原因是子查询仅关联了Reference字段，没有关联UserName，导致同一Reference下所有用户的OrderId都被聚合。只需在子查询的WHERE条件中添加UserName的关联即可：

SELECT
    tbl2.Reference, tbl2.UserName,
    STUFF((SELECT ',' + CAST(tbl1.OrderId AS varchar (30))
           FROM Table tbl1
           WHERE tbl1.Reference = tbl2.Reference
             AND tbl1.UserName = tbl2.UserName -- 新增UserName关联条件
           FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, 1, '') AS Orders
FROM
    Table tbl2
GROUP BY
    tbl2.UserName, tbl2.Reference 

简洁实现方案（SQL Server 2017+） #

如果使用的是SQL Server 2017及以上版本，可使用STRING_AGG函数实现更简洁的分组聚合：

SELECT
    Reference,
    UserName,
    STRING_AGG(CAST(OrderId AS varchar(30)), ',') AS Orders
FROM Table
GROUP BY Reference, UserName

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