oracle 行列互转 pivot 和unpivot

行转列pivot

创建表

with data_tab as(
select '1991' year,1 month,11 amount from dual union all
select '1991' year,2 month,12 amount from dual union all
select '1991' year,3 month,13 amount from dual union all
select '1991' year,4 month,14 amount from dual union all
select '1992' year,1 month,21 amount from dual union all
select '1992' year,2 month,22 amount from dual union all
select '1992' year,3 month,23 amount from dual union all
select '1992' year,4 month,24 amount from dual 
)

select * from data_tab;

获取每月数据

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 data_tab
group by year;

使用pivot

将原表中一个字段month的数据 1,2,3,4 分别作为一个新字段，
另一个字段amount聚合后作为新字段的数据，
在聚合时用year字段来group

for 原表字段/将被删除 in (如果数值是X1 新字段1，…如果数值是Xn 新字段n)
sum(新字段的数据取自原表另一个字段)

select *
  from data_tab 
  pivot(sum(amount) amo for month in(1 m1, 2 m2, 3 m3, 4 m4))

去掉 amo 以后

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

增加查询条件

select *
  from data_tab 
  pivot(sum(amount) for month in(1 m1, 2 m2, 3 m3, 4 m4))
 where year = 1991

列转行unpivot

创建表

with tab as(
select '1991' year,11 M1,12 M2,13 M3,14 M4 from dual union all
select '1992' year,21 M1,22 M2,23 M3,24 M4 from dual )

select * from tab;

使用unpivot

将表中的几个字段m1，m2，…变成新的month字段的数据
将m1,m2这几个字段的数据，变成新字段amount的数据
year字段不变
一次对每一行重复以上步骤

(新增字段amount for 新增字段month in (原字段m1,m2…))

select * from tab
unpivot 
(amount for month in (m1,m2,m3,m4) );

处理空字段 include nulls

with tab as(
select '1991' year,11 M1,12 M2,13 M3,14 M4 from dual union all
select '1992' year,21 M1,22 M2,23 M3,null M4 from dual )

select * from tab
unpivot include nulls
(amount for month in (m1,m2,m3,m4) )