Oracle利用PIVOT和UNPIVOT进行行列转换

在数据库开发中，我们会经常遇到行转列和列转行的情况，方法很多，比如使用CASE...WHEN...、DECODE，或者PIVOT和UNPIVOT都可以实现该功能，本篇讲解下PIVOT和UNPIVOT的使用，希望可以让读者举一反三，了解行列转换的用法。

PIVOT语法

SELECT ...

   FROM ...

   PIVOT [XML] 

            ( pivot_clause 

               pivot_for_clause 

               pivot_in_clause )

WHERE ...

说明：

pivot_clause：定义将要进行聚合操作的列；

pivot_for_clause：定义将要进行分组和转换的列；

pivot_in_clause：定义在pivot_for_clause中对列的过滤，对于pivot_in_clause中每一个值进行，并转换为单个列。

pivot_clause::=

pivot_for_clause::=

pivot_in_clause::=

演示（利用Oracle的样例数据库，Schema为SCOTT）：

1、首先我们产生一些基本数据，用于演示的基础；

SQL> select job,deptno ,sum(sal) as sum_sal   2    from emp  3   group by job,deptno  4   order by job,deptno;JOB      DEPTNOSUM_SAL--------- ---------- ----------ANALYST   20   6000CLERK  10   1300CLERK  20   1900CLERK  30    950MANAGER   10   2450MANAGER   20   2975MANAGER   30   2850PRESIDENT  10   5000SALESMAN  30   61009 rows selected.

2、使用WITH..as利用PIVOT对DEPTNO进行转列；

SQL> with pivot_emp as(  2       select job,deptno,sal from emp)  3  select *  4    from pivot_emp  5   pivot(  6         sum(sal)   7          for deptno               in(10 as dept_10,20 as dept_20,30 as dept_30,40 as dept_40)  8        );JOB     DEPT_10DEPT_20    DEPT_30    DEPT_40--------- ---------- ---------- ---------- ----------CLERK1300   1900        950SALESMAN      6100PRESIDENT5000MANAGER 2450   2975       2850ANALYST    6000

3、也可以使用内联视图利用PIVOT对DEPTNO进行转列，和上面的结果相同；

SQL> select *from (select sal,deptno,job from emp)  2  pivot (  3  sum(sal)  4  for deptno in(10,20,30,40)  5  );JOB  10     20 30   40--------- ---------- ---------- ---------- ----------CLERK1300   1900        950SALESMAN      6100PRESIDENT5000MANAGER 2450   2975       2850ANALYST    6000SQL> 

4、查询整个表，对DEPTNO进行转换；

SQL> select *from emp  2  pivot(  3  sum(sal) for deptno in (10,20,30,40)  4  );结果省略...

5、PIVOT查询对于别名的使用；

SQL> select *  2    from (select job , deptno , sal from emp)  3    pivot(  4         sum(sal) as sum_sal  5         for deptno   6          in (10 as dept_10 , 20 , 30 , 40 as dept_40)  7         );JOB  DEPT_10_SUM_SAL 20_SUM_SAL 30_SUM_SAL DEPT_40_SUM_SAL--------- --------------- ---------- ---------- ---------------CLERK     13001900    950SALESMAN   6100PRESIDENT     5000MANAGER      24502975   2850ANALYST 6000

说明：pivot_clause中的聚合函数，如果设置了别名，会和pivot_in_clause中的列名或别名进行拼接，如果不设置，则只显示pivot_in_clause中的列名或别名。

6、PIVOT多行查询；

SQL> select *  2     from (select job , deptno , sal from emp)  3      pivot(  4          sum(sal) as sum_sal,  5          count(sal) as cnt  6          for deptno  7           in (10 as dept_10, 20 ,30 ,40)  8            );JOB  DEPT_10_SUM_SAL DEPT_10_CNT 20_SUM_SAL     20_CNT 30_SUM_SAL   30_CNT 40_SUM_SAL 40_CNT--------- --------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------CLERK     1300    1    1900  2   9501      0SALESMAN    0  0  61004      0PRESIDENT     5000    1  00      0MANAGER      2450    1    2975  1  28501      0ANALYST     0    6000  20      0

7、for in含有多列的查询；

SQL> select *  2    from (select job ,deptno, sal from emp)  3    pivot(  4         sum(sal) as sum_sal,count(1) as cnt  5         for (deptno,job)   6          in(  7             (30,'MANAGER') as d30_mgr,  8             (30,'CLERK') as d30_clerk,  9             (30,'SALESMAN') as d30_salman 10             ) 11           );D30_MGR_SUM_SAL D30_MGR_CNT D30_CLERK_SUM_SAL D30_CLERK_CNT D30_SALMAN_SUM_SAL D30_SALMAN_CNT--------------- ----------- ----------------- ------------- ------------------ --------------   2850   1  950  1  6100    4

8、知道列的命名规则，可以在Select字段中列出要查询的列；

SQL> select d30_mgr_sum_sal, d30_mgr_cnt  2    from (select job ,deptno, sal from emp)  3    pivot(  4          sum(sal) as sum_sal,count(1) as cnt  5           for (deptno,job)   6           in(  7              (30,'MANAGER') as d30_mgr,  8              (30,'CLERK') as d30_clerk,  9              (30,'SALESMAN') as d30_salman 10             ) 11           );D30_MGR_SUM_SAL D30_MGR_CNT--------------- -----------   2850   1

9、查看用PIVOT进行转换的执行计划，其实也对CASE...WHEN的改写而已；

SCOTT@orcl _SQL>explain plan set statement_id='pivot'  2  for  3  select *  4    from (select job,deptno,sal from emp)  5   pivot(  6        sum(sal)  7        for deptno   8         in (10 as dept_10,20 as dept_20,30 as dept_30,40 as dept_40)  9       );Explained.SCOTT@orcl _SQL>select *from table(dbms_xplan.display(null,'pivot','TYPICAL +PROJECTION'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1475541029----------------------------------------------------------------------------| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |----------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   | 5 |75 | 4  (25)| 00:00:01 ||   1 |  HASH GROUP BY PIVOT|   | 5 |75 | 4  (25)| 00:00:01 ||   2 |   TABLE ACCESS FULL | EMP  |14 |   210 | 3   (0)| 00:00:01 |----------------------------------------------------------------------------Column Projection Information (identified by operation id):PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------   1 - (#keys=1) "JOB"[VARCHAR2,9], SUM(CASE  WHEN ("DEPTNO"=10) THEN       "SAL" END )[22], SUM(CASE  WHEN ("DEPTNO"=20) THEN "SAL" END )[22],       SUM(CASE  WHEN ("DEPTNO"=30) THEN "SAL" END )[22], SUM(CASE  WHEN       ("DEPTNO"=40) THEN "SAL" END )[22]   2 - "JOB"[VARCHAR2,9], "SAL"[NUMBER,22], "DEPTNO"[NUMBER,22]18 rows selected.

UNPIVOT语法

SELECT ...

   FROM ...

UNPIVOT [INCLUDE|EXCLUDE NULLS] 

       ( unpivot_clause 

         unpivot_for_clause 

         unpivot_in_clause )

WHERE ...

unpivot_clause::=

unpivot_in_clause::=

演示：

1、创建一个view，用于存放基础数据；

SCOTT@orcl _SQL>create view pivoted_emp as  2    select *  3      from (select job,deptno,sal from emp)  4      pivot (  5              sum(sal)  6              for deptno   7              in (10 as dept10,20 as dept20,30 as dept30,40 as dept40)  8             );SCOTT@orcl _SQL>select *from pivoted_emp;JOB      DEPT10 DEPT20     DEPT30     DEPT40--------- ---------- ---------- ---------- ----------CLERK1300   1900        950SALESMAN      6100PRESIDENT5000MANAGER 2450   2975       2850ANALYST    6000SCOTT@orcl _SQL>

2、利用UNPIVOT进行列转行操作；

SCOTT@orcl _SQL>select *  2  from pivoted_emp  3  unpivot (  4  sal   5  for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40)  6  );JOB      DEPTNO    SAL--------- ---------- ----------CLERK  10   1300CLERK  20   1900CLERK  30    950SALESMAN  30   6100PRESIDENT  10   5000MANAGER   10   2450MANAGER   20   2975MANAGER   30   2850ANALYST   20   60009 rows selected.SCOTT@orcl _SQL>

3、从上面的结果显示，不包含NULL值的JOB，如果显示出来，需要使用INCLUDE关键字；

SCOTT@orcl _SQL>select *  2    from pivoted_emp  3    unpivot include nulls(  4    sal  5    for deptno in (dept10 as 10,dept20 as 20,dept30 as 30, dept40 as 40)  6  );JOB      DEPTNO    SAL--------- ---------- ----------CLERK  10   1300CLERK  20   1900CLERK  30    950CLERK  40SALESMAN  10SALESMAN  20SALESMAN  30   6100SALESMAN  40PRESIDENT  10   5000PRESIDENT  20PRESIDENT  30JOB      DEPTNO    SAL--------- ---------- ----------PRESIDENT  40MANAGER   10   2450MANAGER   20   2975MANAGER   30   2850MANAGER   40ANALYST   10ANALYST   20   6000ANALYST   30ANALYST   40