ORACLE PIVOT函数
语法
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230725125524376-1626648601.png)
函数用途: PIVOT函数被用于SELECT子句中,在查询时将行转换成列,并在由行转列时对对应数据进行聚合统计.一般使用PIVOT操作后结果集将比原结果集包含更多的列,以及更少的行。
计算步骤:
-
1.PIVOT函数计算位于PIVOT子句起始位置的聚合函数。使用聚合函数必须要定义一个GOURP BY 子句以返回分组统计值,但在PIVOT函数中不显式使用GROUP BY 子句,实际上PIVOT函数使用隐式(默认)的 GROUP BY 子句。默认GROUP BY 使用的分组字段为在PIVOT子句中未使用到的所有字段,以及将要行转列的字段里所有的值。即除了聚合函数里以外的所有字段,以及比如将要行转列的字段(假设NAME字段)里的所有不同值,都是PIVOT函数里默认GROUP BY 使用的分组字段。如果在PIVOT函数中定义了多个聚合函数,那么则必须为除一个以外的所有其他聚合函数重命名(即只允许有一个聚合函数不进行重命名操作)。
-
2.分组列以及聚合字段在第一步计算这后将以以下方式输出结果集:
a .PIVOT函数中未出现的所有的隐式分组字段按以下方式计算展示:
b.结果集中新输出的列与PIVOT函数中的PIVOT_IN_CLAUSE参数相关。PIVOT_IN_CLAUSE参数代表要转成列的行的所有不同值。比如要将NAME 字段里的行值:FEDERER,NADAL,NOVAK三个值转成三个字段(列),那么PIVOT_IN_CLAUSE参数位置即是FEDERER,NADAL,NOVAK值,最终这个参数里的所有值将被转换成列(字段)值。如果PIVOT函数里的XML参数值 被定了XML关键字,结果集将被输出为仅为一个字段的XML字符串。(关于PIVOT函数的含XML关键字的形式,以及与XML关键配合使用的expr,subquery,any参数,可自行参考文档,目前没用过,暂不叙述,后续有用到再补充)。PIVOT_IN_CLAUSE参数中可以为每个要转列并聚合计算的值重命名,不进行重命名时,其行值即为新列名称。注意ORACLE所有表、字段命名长度不能超过30 byte,否则会报ORA_00918错误。
示例:
通过安装oralce默认创建的oe.orders进行查询: -
CREATE TABLE pivot_table AS SELECT * FROM (SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM oe.orders) PIVOT (SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet)); SELECT * FROM pivot_table ORDER BY year;
EXTRACT语句查询集:
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230725125524419-611578273.png)
PIVOT函数对EXTRACT语句查询集的ORDER_DATE列值行转列操作后:
![](https://img2023.cnblogs.com/blog/2755468/202307/2755468-20230725125524379-1375863704.png)
EXTRACT函数仅将ORDERDATE 字段转为年份形式。PIVOT函数将子表(三个字段:YEAR,ORDERMODE,ORDERTOTAL)中的ordertotal进行聚合(即aggregatefunction参数),要转为列的是ORDERMODE字段(即PIVOT_FOR_CLAUSE参数)中的'direct','online'两个行值,(即PIVOT_IN_CLAUSE参数)此时分组字段默认为YEAR,以及direct,online.即GROUP BY YEAR,DIRECT,ONLINE(GROUP BY 不支持别名)。
参考文档 ORALCE官网 SQL Language Reference:https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/About-SQL-Functions.html#GUID-D51AB228-518C-4213-8BD4-F919623D105E