无需存储过程实现PL/SQL动态透视表的技术咨询

你之前的PIVOT写法核心错误在于：把透视的字段指定为了PROD_QUANTITY，但实际需要作为透视列的应该是日期PROD_DATE，这直接导致结果不符合预期。下面提供两种可行的解决思路：

方案一：用PIVOT XML实现动态透视（推荐，无需提前枚举所有日期） #

先修正PIVOT的逻辑，再通过XMLTABLE解析XML结果，得到结构化的透视数据：

WITH A AS (
    SELECT OH.Part_Product_Family PROD_GROUP,
           SUM(OH.QTY_COMPLETE) PROD_QUANTITY,
           TO_CHAR(OH.DATE_APPLIED, 'YYYY-MM-DD') PROD_DATE -- 转成字符串避免XML日期格式冲突
      FROM OPERATION_HISTORY OH, SHOP_ORD SO, CUSTOMER_ORDER_LINE_CFV COL
     WHERE OH.DATE_APPLIED >= TO_DATE(&FIRST_DATE, 'YYYY/MM/DD')
       AND OH.DATE_APPLIED <= TO_DATE(&LAST_DATE, 'YYYY/MM/DD')
       -- 重点：原查询未关联SO和COL表，会产生笛卡尔积，务必补充关联条件，比如 OH.SHOP_ORD_ID = SO.ID 这类逻辑
     GROUP BY OH.Part_Product_Family, TO_CHAR(OH.DATE_APPLIED, 'YYYY-MM-DD')
)
SELECT p.PROD_GROUP,
       x.prod_date,
       x.quantity
  FROM A
 PIVOT XML (
    SUM(PROD_QUANTITY) AS qty
    FOR PROD_DATE IN (ANY) -- 动态匹配查询范围内的所有日期
 ) p,
 XMLTABLE(
    '/PivotSet/item'
    PASSING p.XMLDATA
    COLUMNS 
        prod_date VARCHAR2(10) PATH '@column',
        quantity NUMBER PATH 'qty'
 ) x
ORDER BY p.PROD_GROUP, x.prod_date;

如果需要完全模拟Excel透视表的日期作为列的布局，可以用动态SQL拼接实现，无需存储过程，直接在客户端工具（如PL/SQL Developer、SQL*Plus）执行：

方案二：动态拼接SQL实现列布局透视表 #

-- 第一步：查询范围内的所有日期，拼接成透视列的SQL片段
SELECT LISTAGG(
    'SUM(CASE WHEN PROD_DATE = ''' || PROD_DATE || ''' THEN PROD_QUANTITY END) AS "' || PROD_DATE || '"', 
    ', '
) WITHIN GROUP (ORDER BY PROD_DATE)
INTO :cols -- 不同客户端变量写法不同，SQL*Plus用DEFINE，PL/SQL Developer用绑定变量
FROM (
    SELECT DISTINCT TO_CHAR(OH.DATE_APPLIED, 'YYYY-MM-DD') PROD_DATE
      FROM OPERATION_HISTORY OH
     WHERE OH.DATE_APPLIED >= TO_DATE(&FIRST_DATE, 'YYYY/MM/DD')
       AND OH.DATE_APPLIED <= TO_DATE(&LAST_DATE, 'YYYY/MM/DD')
);

-- 第二步：执行拼接后的完整SQL
EXECUTE IMMEDIATE '
    SELECT PROD_GROUP, ' || :cols || '
      FROM (
          SELECT OH.Part_Product_Family PROD_GROUP,
                 SUM(OH.QTY_COMPLETE) PROD_QUANTITY,
                 TO_CHAR(OH.DATE_APPLIED, ''YYYY-MM-DD'') PROD_DATE
            FROM OPERATION_HISTORY OH, SHOP_ORD SO, CUSTOMER_ORDER_LINE_CFV COL
           WHERE OH.DATE_APPLIED >= TO_DATE(''&FIRST_DATE'', ''YYYY/MM/DD'')
             AND OH.DATE_APPLIED <= TO_DATE(''&LAST_DATE'', ''YYYY/MM/DD'')
             -- 补充表关联条件
           GROUP BY OH.Part_Product_Family, TO_CHAR(OH.DATE_APPLIED, ''YYYY-MM-DD'')
      )
     GROUP BY PROD_GROUP
     ORDER BY PROD_GROUP';

重要提醒 #

原查询中SHOP_ORD SO和CUSTOMER_ORDER_LINE_CFV COL两张表未添加关联条件，会产生笛卡尔积，导致统计的数量完全错误，必须根据实际表结构补充关联逻辑（比如订单ID、行ID的关联）。

内容的提问来源于stack exchange，提问作者halil balcıoğlu