PIVOT子句使用动态表达式报错求助：非常量表达式不被允许

问题原因 #

Oracle的PIVOT语法要求IN子句中的值必须是常量，不能使用字段或表达式（你写的'to_char(oh.created_date, 'MON-YYYY')'既不是合法常量，也无法动态生成月份值），这就是报错non-constant expression is not allowed for pivot|unpivot values的核心原因。

解决方案 #

方案1：固定目标月份（比如你需要的SEP-2023） #

直接将IN子句替换为明确的月份常量，同时建议给列名起别名（避免带引号的难用列名）：

SELECT
    *
FROM
    (
        SELECT
            gt.tp_short_name                     AS store,
            to_char(oh.created_date, 'MON-YYYY') AS month_val,
            oh.order_ref_no                      AS order_id
        FROM
            order_hdr    oh,
            gnrl_tp      gt,
            gnrl_tp_type gtt
        WHERE
                oh.customer_id = gt.tp_id
            AND gt.tp_id = gtt.tp_id
            AND trunc(oh.created_date) >= ?
            AND trunc(oh.created_date) < ?
            AND oh.service_type IN ( ? )
            AND gtt.tp_type = 'TPTYPE-CUSTOMER'
            AND gt.active_ind = 1
            AND gt.inact_date IS NULL
            AND oh.inact_date IS NULL
            AND gt.tp_short_name IN (
                SELECT
                    org_short_name
                FROM
                    gnrl_org
                WHERE
                    org_id IN ( ?, ? )
            )
        GROUP BY
            gt.tp_short_name,
            to_char(oh.created_date, 'MON-YYYY'),
            oh.order_ref_no
        ORDER BY
            gt.tp_short_name,
            to_char(oh.created_date, 'MON-YYYY')
    ) PIVOT (
        COUNT(order_id)
        FOR month_val
        IN ('SEP-2023' AS sep_2023)  -- 明确常量值，同时指定列别名
    )
ORDER BY
    store ASC

方案2：动态生成月份值（适配日期参数范围） #

如果需要根据trunc(oh.created_date) >= ?和trunc(oh.created_date) < ?的参数动态生成月份列表，Oracle原生PIVOT不支持动态IN子句，需要用动态SQL实现：

DECLARE
    v_month_list VARCHAR2(1000);
    v_sql        VARCHAR2(4000);
BEGIN
    -- 查询日期范围内的所有月份，拼接成PIVOT需要的常量格式
    SELECT LISTAGG('''' || month_val || ''' AS ' || LOWER(month_val), ', ')
           INTO v_month_list
    FROM (
        SELECT DISTINCT to_char(oh.created_date, 'MON-YYYY') AS month_val
        FROM order_hdr oh
        WHERE trunc(oh.created_date) >= ?
          AND trunc(oh.created_date) < ?
    );

    -- 拼接动态SQL
    v_sql := '
        SELECT *
        FROM (
            SELECT
                gt.tp_short_name                     AS store,
                to_char(oh.created_date, ''MON-YYYY'') AS month_val,
                oh.order_ref_no                      AS order_id
            FROM
                order_hdr    oh,
                gnrl_tp      gt,
                gnrl_tp_type gtt
            WHERE
                    oh.customer_id = gt.tp_id
                AND gt.tp_id = gtt.tp_id
                AND trunc(oh.created_date) >= ?
                AND trunc(oh.created_date) < ?
                AND oh.service_type IN ( ? )
                AND gtt.tp_type = ''TPTYPE-CUSTOMER''
                AND gt.active_ind = 1
                AND gt.inact_date IS NULL
                AND oh.inact_date IS NULL
                AND gt.tp_short_name IN (
                    SELECT
                        org_short_name
                    FROM
                        gnrl_org
                    WHERE
                        org_id IN ( ?, ? )
                )
            GROUP BY
                gt.tp_short_name,
                to_char(oh.created_date, ''MON-YYYY''),
                oh.order_ref_no
        ) PIVOT (
            COUNT(order_id)
            FOR month_val
            IN (' || v_month_list || ')
        )
        ORDER BY store ASC';

    -- 执行动态SQL（如需返回结果，可改用REF CURSOR输出）
    EXECUTE IMMEDIATE v_sql;
END;
/

补充优化建议 #

原查询中的GROUP BY oh.order_ref_no可能多余：如果你的需求是统计每个门店每月的订单总数，不需要按订单号分组，直接按store和month_val分组即可，这样COUNT(order_id)会得到正确的订单数量，而非每个订单单独计数1。优化后的子查询如下：

SELECT
    gt.tp_short_name                     AS store,
    to_char(oh.created_date, 'MON-YYYY') AS month_val,
    COUNT(oh.order_ref_no)               AS order_count
FROM
    order_hdr    oh,
    gnrl_tp      gt,
    gnrl_tp_type gtt
WHERE
        oh.customer_id = gt.tp_id
    AND gt.tp_id = gtt.tp_id
    AND trunc(oh.created_date) >= ?
    AND trunc(oh.created_date) < ?
    AND oh.service_type IN ( ? )
    AND gtt.tp_type = 'TPTYPE-CUSTOMER'
    AND gt.active_ind = 1
    AND gt.inact_date IS NULL
    AND oh.inact_date IS NULL
    AND gt.tp_short_name IN (
        SELECT
            org_short_name
        FROM
            gnrl_org
        WHERE
            org_id IN ( ?, ? )
    )
GROUP BY
    gt.tp_short_name,
    to_char(oh.created_date, 'MON-YYYY')
ORDER BY
    gt.tp_short_name,
    to_char(oh.created_date, 'MON-YYYY')

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