如何用PIVOT函数编写日期列动态SQL并解决列名单引号问题

修改后的PL/SQL代码 #

DECLARE
    v_sql VARCHAR2(4000);
    v_months VARCHAR2(1000);
    v_month_cols VARCHAR2(1000);
    l_month VARCHAR2(10); -- 临时存储单个月份字符串
BEGIN
    -- 动态生成PIVOT子句的月份项（带别名去除单引号）和求和用的列名字符串
    FOR i IN 0..11 LOOP
        l_month := TO_CHAR(ADD_MONTHS(SYSDATE, i), 'MonYYYY');
        -- 构造PIVOT IN子句的项：'月份值' AS 列名
        v_months := v_months || '''' || l_month || ''' AS ' || l_month || ', ';
        -- 构造总计列的求和表达式，用COALESCE处理NULL值
        v_month_cols := v_month_cols || 'COALESCE(' || l_month || ', 0) + ';
    END LOOP;

    -- 去除字符串末尾多余的逗号、空格和加号
    v_months := RTRIM(v_months, ', ');
    v_month_cols := RTRIM(v_month_cols, ' + ');

    -- 构建最终的动态SQL，添加总计列
    v_sql := 'CREATE TABLE dynamic_pivot_table_test AS ' ||
             'SELECT t.*, (' || v_month_cols || ') AS 总计 ' ||
             'FROM (' ||
             'SELECT part, TO_CHAR(begin_date, ''MonYYYY'') AS begin_date_YM, SUM(new_buy_units) AS SUM_UNITS ' ||
             'FROM one_mpbr ' ||
             'WHERE begin_date >= TRUNC(SYSDATE, ''MM'') ' ||
             'AND begin_date < ADD_MONTHS(TRUNC(SYSDATE, ''MM''), 12) ' ||
             'GROUP BY part, TO_CHAR(begin_date, ''MonYYYY'')) ' ||
             'PIVOT (SUM(SUM_UNITS) FOR begin_date_YM IN (' || v_months || ')) t';

    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE v_sql;
END;
/

关键修改说明 #

1. 去除列名单引号
   原代码仅在PIVOT的IN子句中指定带单引号的月份匹配值，未设置列别名。修改后为每个月份项添加AS 列名（如'Jan2024' AS Jan2024），生成的列名即为无单引号的纯月份字符串。

2. 添加总计列

   • 新增v_month_cols变量动态生成求和表达式，用COALESCE将NULL值转为0，避免因某月份无数据导致求和结果为NULL。
   • 在主查询中通过(' || v_month_cols || ') AS 总计计算每个part的各月销量总和，作为新增的总计列。

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