oracle pivot / unpivot

1.pivot 行转列

   pivot 用法：

select ...
from ...
pivot --> 注意：在from 与where 之间的关键字
      (pivot_clause
       pivot_for_clause
       pivot_in_clause)
where ...
(1)pivot_clause:定义要进行聚集的列；
(2)pivot_for_clause:定义要分组和转置的列；
(3)pivot_in_clause:定义限定结果的值的范围。产生的每个值的聚集转换为单独一列。

   pivot 注意事项：

   (1).任何仅在pivot子句中引用的列，不能用在select 列表中；

   (2).任何仅在pivot for 子句中引用的列，不能用在select 列表中；

   (3).pivot 子句中的所有列都必须使用聚集函数。

 

   1.1 示例：单个字段聚集

create table pivot_table as
select *
  from (select t.job, t.deptno, t.sal from emp t)
pivot(sum(sal) --pivot_clause     定义要进行聚集的列
   for deptno  --pivot_for_clause 定义要分组和转置的列
     in(10 dept_10, 20 dept_20, 30 dept_30))
               --pivot_in_clause  定义限定结果集的值的范围。产生的每个值的聚集转换为单独一列。

   1.2 示例：多个字段聚集

--pivot 使用多个聚集
select *
  from (select t.job, t.deptno, t.sal from emp t)
pivot(sum(sal) sal,count(sal) cnt
   for deptno  
     in(10 dept_10, 20 dept_20, 30 dept_30))
  where job in ('MANAGER','CLERK');

 

2.unpivot 列转行

   unpivot 用法：

select ...
  from ...
  pivot [include nulls|exclude nulls]
        (unpivot_clause
         unpivot_for_clause
         unpivot_in_clause)
  where ...
  (1)unpivot_clause: 定义表示反转置值后的列名称(列名对应的列值)；
  (2)unpivot_for_clause: 定义反转置查询所得到列的列名称(列名)；
  (3)unpivot_in_clause: 定义要进行反转置的已转置列(不是值)的列表。

 

   2.1 示例：列转行 ，在unpivot_in_clause 中可以使用 as 使用别名

select * 
  from (select * from pivot_table )
  unpivot (sal_value  
    for dept_column
      in (dept_10 as '10' ,dept_20,dept_30)); --使用别名

 

   2.2 示例：(1) include nulls ; (2) 转置数据类型需一致

--unpivot 列传行
/* 使用unpivot函数，所有转置列数据类型需要一致，这是UNPIVOT查询执行上的一个限制，
   否则会报错：ora-01790 */
   
select *
  from (select to_char(empno) empno,
               t.ename,
               t.job,
               to_char(t.mgr) mgr,
               to_char(t.hiredate,'yyyy-mm-dd') hiredate,
               to_char(t.sal) sal,
               to_char(t.comm) comm,
               to_char(t.deptno) deptno
          from emp t
         where rownum = 1)
  unpivot include nulls(colnum_value
      for colnum_name
        in(empno,ename,job,mgr,hiredate,sal,comm,deptno)) ;