SSMS2016中SQL按ORDER_NUMBER分组并合并ITEM_CAT值至单行

完全可以轻松实现这个需求！针对你使用的SSMS 2016环境（对应SQL Server 2016版本），我们可以通过FOR XML PATH来拼接同一订单下的品类名称，同时完成数量的汇总。如果你的数据库实际是SQL Server 2017及以上版本（SSMS 2016也能连接高版本数据库），还可以用更简洁的STRING_AGG函数，下面分别给出两种方案：

方案1：兼容SQL Server 2016的写法 #

这种方法利用FOR XML PATH来拼接字符串，结合STUFF去掉开头多余的分隔符，同时完成聚合计算：

WITH GroupedData AS (
    -- 先按订单+品类分组，得到每个品类的数量汇总（和你之前执行的GROUP BY结果一致）
    SELECT 
        ORDER_NUMBER,
        ITEM_CAT,
        SUM(ORDER_QTY) AS GROUPED_QTY,
        SUM(UNALLOCATED) AS GROUPED_UNALLOCATED
    FROM TABLEA
    GROUP BY ORDER_NUMBER, ITEM_CAT
)
SELECT 
    ORDER_NUMBER,
    -- 拼接同一订单下的所有品类，用STUFF移除开头的" / "
    STUFF((
        SELECT ' / ' + ITEM_CAT 
        FROM GroupedData gd2 
        WHERE gd2.ORDER_NUMBER = gd1.ORDER_NUMBER
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 3, '') AS ITEM_CAT,
    -- 汇总整个订单的总数量和未分配数量
    SUM(GROUPED_QTY) AS ORDER_QTY,
    SUM(GROUPED_UNALLOCATED) AS UNALLOCATED
FROM GroupedData gd1
GROUP BY ORDER_NUMBER
ORDER BY ORDER_NUMBER;

方案2：SQL Server 2017+ 简洁写法 #

如果你的数据库版本支持STRING_AGG函数（SQL Server 2017及以上），可以用更直观的方式实现：

SELECT 
    ORDER_NUMBER,
    -- 直接用STRING_AGG拼接品类，指定分隔符为" / "
    STRING_AGG(ITEM_CAT, ' / ') AS ITEM_CAT,
    SUM(ORDER_QTY) AS ORDER_QTY,
    SUM(UNALLOCATED) AS UNALLOCATED
FROM (
    -- 先按订单+品类分组，避免重复拼接同一品类
    SELECT 
        ORDER_NUMBER,
        ITEM_CAT,
        SUM(ORDER_QTY) AS ORDER_QTY,
        SUM(UNALLOCATED) AS UNALLOCATED
    FROM TABLEA
    GROUP BY ORDER_NUMBER, ITEM_CAT
) AS GroupedData
GROUP BY ORDER_NUMBER
ORDER BY ORDER_NUMBER;

执行结果 #

两种方案都会得到你期望的输出：

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