PL/SQL中非常量表达式实现Unpivot的解决方案求助

原始数据 #

问题说明 #

需要对上述数据执行Unpivot操作，希望避免手动硬编码月份值，尝试以下代码时触发错误：

select
    car,
    month,
    amount
from TABLE
unpivot exclude nulls
(
    amount for month in
    (
        amount_1 as 202412,
        amount_2 as (select distinct month_2 from TABLE) /* 此处无法运行 */
    )
);

报错信息：

ORA-56901: non-constant expression is not allowed for pivot|unpivot values

Oracle的Unpivot语法要求in子句中的映射值必须是常量，即便子查询结果为单一常量也无法通过这种方式实现。以下是几种可行的替代方案：

解决方案 #

1. 使用UNION ALL静态实现（适合固定列数场景） #

如果数据只有固定的两组month/amount列，直接用UNION ALL拼接对应列，动态取每行的月份值，同时保留exclude nulls的逻辑：

select car, month_1 as month, amount_1 as amount from your_table
union all
select car, month_2 as month, amount_2 as amount from your_table
where amount_2 is not null;

2. 动态SQL生成Unpivot语句（适合列数不固定场景） #

如果后续可能新增更多month_n/amount_n列，可以通过动态SQL自动生成Unpivot的映射关系：

declare
    v_sql varchar2(4000);
    v_unpivot_items varchar2(2000);
begin
    -- 自动拼接所有amount列与对应month列的映射
    select listagg(
        'amount_' || regexp_substr(column_name, '\d+$') || ' as (select distinct month_' || regexp_substr(column_name, '\d+$') || ' from your_table)'
        , ', '
    ) within group (order to_number(regexp_substr(column_name, '\d+$')))
    into v_unpivot_items
    from user_tab_columns
    where table_name = upper('your_table') -- 替换为实际表名，注意大写
      and column_name like 'amount_%';

    -- 组装完整的Unpivot查询
    v_sql := '
        select car, month, amount
        from your_table
        unpivot exclude nulls (
            amount for month in (' || v_unpivot_items || ')
        )
    ';

    -- 执行动态SQL，可根据需求将结果插入临时表或直接输出
    execute immediate v_sql;
end;
/

3. 利用LATERAL JOIN（Oracle 12c及以上版本支持） #

Oracle 12c引入的LATERAL关键字允许关联子查询引用主表的列，可灵活实现类似Unpivot的效果：

select t.car, x.month, x.amount
from your_table t
lateral (
    select t.month_1 as month, t.amount_1 as amount from dual
    union all
    select t.month_2 as month, t.amount_2 as amount from dual
) x
where x.amount is not null; -- 对应exclude nulls逻辑

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