Oracle 通过pivot和unpivot实现行列转换

时间:2022-01-09 00:47:31

数据准备

假定已经创建date_tab表,内容如下:
Oracle 通过pivot和unpivot实现行列转换

行转列

一般实现

使用case when 或 decode 实现,

select year,
sum(decode(month,1,amount,0)) m1,
sum(decode(month,2,amount,0)) m2,
sum(decode(month,3,amount,0)) m3,
sum(decode(month,4,amount,0)) m4
from date_tab
group by year;

使用pivot

Oracle 11g新特性,使用pivot实现行转列

select * from date_tab pivot (sum(amount) amt for month in (1 m1,2 as m2,3 as m3, 4 as m4) );

查询结果:
Oracle 通过pivot和unpivot实现行列转换
也可以增加 查询条件:

select * from date_tab pivot (sum(amount) amt for month in (1 m1,2 as m2,3 as m3, 4 as m4) )
where year=1992;

查询结果:
Oracle 通过pivot和unpivot实现行列转换

列转行

使用unpivot

Oracle 11g新特性,使用unpivot实现列转行
首先根据上述查询语句创建新表:data_tab_pivoted
Oracle 通过pivot和unpivot实现行列转换
通过 unpivot实现列传行:

select * from date_tab_pivoted 
unpivot
(amount for month in (m1_amt,m2_amt,m3_amt,m4_amt));

查询结果:
Oracle 通过pivot和unpivot实现行列转换
若原始表中包含null字段,可通过如下方式进行转换:

select * from date_tab_pivoted 
unpivot include nulls
(amount for month in (m1_amt,m2_amt,m3_amt,m4_amt));

扩展阅读

Oracle 11g 行列互换 pivot 和 unpivot 说明
oracle-developer.net