Oracle中使用PIVOT输出创建表报ORA-00907错误如何解决

错误原因 #

你当前执行的CTAS（CREATE TABLE AS SELECT）语句中，在AS关键字后额外给整段SELECT查询包裹了一层多余的圆括号，Oracle语法解析器处理PIVOT/UNPIVOT这类嵌套子句时，会出现括号配对识别错误，最终抛出ORA-00907: missing right parenthesis报错。单独执行SELECT语句时没有这层多余括号，所以可以正常运行。

解决方案 #

直接删除AS后面包裹SELECT的外层括号即可，正确写法如下：

create table new_table as 
select * from mytable
unpivot(period for months in 
(
"October 2019",
"November 2019",
"December 2019",
"January 2020",
"February 2020",
"March 2020" ))
pivot(
avg(period) 
for PROD_SKU in ('ABC123','DEF456')
)
order by 1;

可选兼容写法 #

如果需要对查询逻辑做封装，也可以将 pivot 结果作为子查询放在FROM从句中，这种写法同样符合Oracle语法规范：

create table new_table as 
select * from (
    select * from mytable
    unpivot(period for months in 
    (
    "October 2019",
    "November 2019",
    "December 2019",
    "January 2020",
    "February 2020",
    "March 2020" ))
    pivot(
    avg(period) 
    for PROD_SKU in ('ABC123','DEF456')
    )
    order by 1
);

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