在SSMS v18中实现订单冻结类型列合并与求和并解决重复问题

问题背景 #

使用SSMS v18处理订单表，表含主键order_id及4个二进制类型的hold_type列（示例列名：hold_type1、hold_type2、hold_type3、hold_type4），需求：

• 将值为1的hold_type列名合并为单个字段，若所有列值均为0则显示'None'
• 按order_id求和所有hold_type的二进制值，生成新列

尝试用UNPIVOT、SUM OVER(PARTITION BY)和STRING_AGG实现时，结果出现重复行。

示例数据 #

原表数据 #

期望结果 #

现有问题代码及重复现象 #

现有SQL代码 #

SELECT 
    u.order_id,
    STRING_AGG(u.hold_column, ',') AS combined_holds,
    SUM(u.hold_value) OVER(PARTITION BY u.order_id) AS total_holds
FROM 
    orders
UNPIVOT (
    hold_value FOR hold_column IN (hold_type1, hold_type2, hold_type3, hold_type4)
) u

重复行示例 #

问题原因：UNPIVOT会将原表每行拆分为对应hold_type列数的行（比如order_id=1拆成2行有效数据），STRING_AGG和窗口SUM会对每个拆分后的行返回相同的聚合结果，导致每个order_id的结果重复出现N次（N为该订单值为1的hold_type列数）。

解决方案 #

方法1：CROSS APPLY + 分组聚合（推荐） #

用VALUES子句替代UNPIVOT，直接筛选值为1的列后分组，避免重复：

SELECT 
    o.order_id,
    COALESCE(STRING_AGG(ca.hold_column, ','), 'None') AS combined_holds,
    COALESCE(SUM(ca.hold_value), 0) AS total_holds
FROM 
    orders o
CROSS APPLY (
    VALUES 
        ('hold_type1', o.hold_type1),
        ('hold_type2', o.hold_type2),
        ('hold_type3', o.hold_type3),
        ('hold_type4', o.hold_type4)
) ca(hold_column, hold_value)
GROUP BY o.order_id

方法2：条件拼接+直接求和（高效无拆分） #

无需拆分表，直接通过条件判断拼接列名，同时求和：

SELECT 
    order_id,
    CASE 
        WHEN hold_type1 + hold_type2 + hold_type3 + hold_type4 = 0 THEN 'None'
        ELSE TRIM(TRAILING ',' FROM CONCAT(
            CASE WHEN hold_type1 = 1 THEN 'hold_type1,' ELSE '' END,
            CASE WHEN hold_type2 = 1 THEN 'hold_type2,' ELSE '' END,
            CASE WHEN hold_type3 = 1 THEN 'hold_type3,' ELSE '' END,
            CASE WHEN hold_type4 = 1 THEN 'hold_type4,' ELSE '' END
        ))
    END AS combined_holds,
    hold_type1 + hold_type2 + hold_type3 + hold_type4 AS total_holds
FROM 
    orders

方法3：UNPIVOT后分组（兼容原有思路） #

如果坚持用UNPIVOT，需先对拆分后的数据分组聚合，避免重复：

WITH unpivoted AS (
    SELECT 
        order_id,
        hold_column,
        hold_value
    FROM 
        orders
    UNPIVOT (
        hold_value FOR hold_column IN (hold_type1, hold_type2, hold_type3, hold_type4)
    ) u
)
SELECT 
    order_id,
    COALESCE(STRING_AGG(hold_column, ','), 'None') AS combined_holds,
    SUM(hold_value) AS total_holds
FROM unpivoted
GROUP BY order_id

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