ORACLE PIVOT函数
语法

函数用途: 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进行查询: -
123456
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语句查询集:

PIVOT函数对EXTRACT语句查询集的ORDER_DATE列值行转列操作后:

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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库