Oracle最新版本中PIVOT列表限制是否仍存在？

是的，截至Oracle的最新版本（包括Oracle 21c、23c），PIVOT子句的IN列表仍要求是静态字面量集合，不支持直接通过SELECT查询动态生成列值组合。你提供的第二个查询无法执行，核心原因就是违反了这一限制——Oracle不允许在PIVOT的IN子句中嵌套SELECT * FROM T3这类动态查询来指定透视列。

可正常运行的查询 #

WITH DATA (A,DATE_FIELD,C,D) AS (
  SELECT 1, TO_DATE('01-JAN-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('01-FEB-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('20-MAR-2013'), 'world',  20 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('21-APR-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('21-JUN-2013'), 'tree',   30 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('01-JUL-2013'), 'world',  10 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('30-JUL-2013'), 'world',  20 FROM DUAL UNION ALL
  SELECT 3, TO_DATE('30-JUL-2013'), 'tree',   30 FROM DUAL UNION ALL
  SELECT 3, TO_DATE('30-JUL-2013'), 'world',  30 FROM DUAL)
SELECT * FROM (
  SELECT   A, C, TO_NUMBER(TO_CHAR (DATE_FIELD, 'd')) AS DAY, SUM(D) D
  FROM DATA
  GROUP BY A, C, TO_CHAR (DATE_FIELD, 'd')
) PIVOT ( SUM(D) FOR (A,DAY) IN (
(1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(1, 6),(1, 7),(1, 8),(1, 9),(1,10),
(1,11),(1,12),(1,13),(1,14),(1,15),(1,16),(1,17),(1,18),(1,19),(1,20),
(1,21),(1,22),(1,23),(1,24),(1,25),(1,26),(1,27),(1,28),(1,29),(1,30),
(1,31),(2, 1),(2, 2),(2, 3),(2, 4),(2, 5),(2, 6),(2, 7),(2, 8),(2, 9),
(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16),(2,17),(2,18),(2,19),
(2,20),(2,21),(2,22),(2,23),(2,24),(2,25),(2,26),(2,27),(2,28),(2,29),
(2,30),(2,31)
))

无法运行的查询 #

WITH DATA (A,DATE_FIELD,C,D) AS (
  SELECT 1, TO_DATE('01-JAN-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('01-FEB-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('20-MAR-2013'), 'world',  20 FROM DUAL UNION ALL
  SELECT 1, TO_DATE('21-APR-2013'), 'tree',   10 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('21-JUN-2013'), 'tree',   30 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('01-JUL-2013'), 'world',  10 FROM DUAL UNION ALL
  SELECT 2, TO_DATE('30-JUL-2013'), 'world',  20 FROM DUAL UNION ALL
  SELECT 3, TO_DATE('30-JUL-2013'), 'tree',   30 FROM DUAL UNION ALL
  SELECT 3, TO_DATE('30-JUL-2013'), 'world',  30 FROM DUAL),
T1 AS (SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 2),
T2 AS (SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 31),
T3 AS (SELECT * FROM T1 CROSS JOIN T2)
SELECT * FROM (
  SELECT   A, C, TO_NUMBER(TO_CHAR (DATE_FIELD, 'd')) AS DAY, SUM(D) D
  FROM DATA
  GROUP BY A, C, TO_CHAR (DATE_FIELD, 'd')
) PIVOT ( SUM(D) FOR (A,DAY) IN (SELECT * FROM T3))

替代解决方案 #

如你实际使用的方案，要实现动态列的透视需求，目前Oracle支持的可行方式有两种：

• PIVOT XML：允许动态生成透视列，结果以XML格式返回，可通过XML解析函数提取所需数据
• 动态SQL：在运行时拼接生成包含静态IN列表的PIVOT查询，这也是你当前采用的方案

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