Oracle——行转列、列转行函数（pivot 和 unpivot函数）

pivot

语法：

（聚合函数 for 列名 in（类型））

注：其中 in(‘’) 中可以指定别名，in中还可以指定子查询。

例子：

select * from 
(
select 333 nums,'苹果' names from dual 
union all
select 555 nums,'香蕉' names from dual 
union all
select 777 nums,'鸭梨' names from dual
) pivot(sum(nums) for names in ('苹果','香蕉','鸭梨'))

unpivot

注：没有聚合函数

例子：

select nums,names from (                                                                    
select 
   333 苹果,
   555 香蕉,
   666 鸭梨
from dual )
  unpivot(names for nums in(苹果,香蕉,鸭梨)) 

 

例2：

创建表A

create table A 
(
ssyf varchar2(10),
num1 number,
num2 number,
num3 number
);

往表A插入数据

SSYF     NUM1 NUM2 NUM3  SZ
201901    1    2    3    a
201902    2    2    2    a
201903    3    3    3    a
201904    4    4    4    a
201905    5    5    5    a
201906    6    6    6    a
201907    7    7    7    a
201908    8    8    8    a
201909    9    9    9    a
201910    10    10    10    a
201911    11    11    11    a
201912    12    12    12    a
201901    1    1    1    b
201902    2    2    2    b
201903    3    3    3    b
201904    4    4    4    b
201905    5    5    5    b
201906    6    6    6    b
201907    7    7    7    b
201908    8    8    8    b
201909    9    9    9    b
201910    10    10    10    b
201911    11    11    11    b
201912    12    12    12    b

代码和结果如下

1.

select *
  from a
pivot(sum(num1) num1, sum(num2) num2, sum(num3) num3
   for ssyf in('201901',
               '201902',
               '201903',
               '201904',
               '201905',
               '201906',
               '201907',
               '201908',
               '201909',
               '201910',
               '201911',
               '201912'))

结果：

 

 2.

select shu1, shu2, zdmc, ssyf, sz
  from a unpivot((shu1, shu2) for zdmc in((num1, num2) as
                                           '1and2',
                                           (num2, num3) as
                                           '2and3',
                                           (num1, num3) as
                                           '1and3'))
 where ssyf = '201901'
   and sz = 'a'