DB2/AS400 SQL透视表实现求助：按月汇总商品数据

针对你的需求，我们可以通过CASE表达式实现行转列的透视效果，再结合UNION ALL添加总计行，以下是适配DB2/AS400的完整SQL代码：

SELECT 
    CODART,
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 1 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "1",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 2 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "2",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 3 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "3",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 4 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "4",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 5 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "5",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 6 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "6",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 7 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "7",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 8 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "8",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 9 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "9",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 10 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "10",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 11 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "11",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 12 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "12"
FROM YOUR_TABLE_NAME
GROUP BY CODART

UNION ALL

SELECT 
    'TOTAL' AS CODART,
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 1 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "1",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 2 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "2",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 3 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "3",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 4 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "4",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 5 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "5",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 6 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "6",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 7 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "7",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 8 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "8",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 9 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "9",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 10 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "10",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 11 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "11",
    COALESCE(CAST(NULLIF(SUM(CASE WHEN MONTH = 12 THEN QT ELSE 0 END), 0) AS VARCHAR(10)), '') AS "12"
FROM YOUR_TABLE_NAME

代码说明 #

1. 透视逻辑：通过CASE WHEN筛选对应月份的QT值并求和，NULLIF将求和结果为0的情况转为NULL，再通过COALESCE将NULL转为空字符串，匹配你需要的显示效果。
2. 分组统计：第一部分按CODART分组，得到每个商品的各月数量；第二部分通过UNION ALL拼接总计行，计算所有商品每个月的总数量，并将CODART设为'TOTAL'。
3. 注意事项：请将代码中的YOUR_TABLE_NAME替换为实际的表名；如果允许显示0而非空字符串，可以去掉NULLIF和COALESCE，直接保留SUM(CASE...)的数值结果。

内容的提问来源于stack exchange，提问作者Luca C.