你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle的行列转换

[日期:2012-09-05] 来源:Linux社区  作者:Linux [字体: ]

首先准备如下表格

  1. tony@ORA11GR2> select empno,ename,job,sal,deptno from emp  
  2.   2  order by deptno,job;  
  3.   
  4.      EMPNO ENAME                JOB                       SAL     DEPTNO  
  5. ---------- -------------------- ------------------ ---------- ----------   
  6.       7934 MILLER               CLERK                    1300         10  
  7.       7782 CLARK                MANAGER                  2450         10  
  8.       7839 KING                 PRESIDENT                5000         10  
  9.       7788 SCOTT                ANALYST                  3000         20  
  10.       7902 FORD                 ANALYST                  3000         20  
  11.       7876 ADAMS                CLERK                    1100         20  
  12.       7369 SMITH                CLERK                     800         20  
  13.       7566 JONES                MANAGER                  2975         20  
  14.       7900 JAMES                CLERK                     950         30  
  15.       7698 BLAKE                MANAGER                  2850         30  
  16.       7654 MARTIN               SALESMAN                 1250         30  
  17.       7521 WARD                 SALESMAN                 1250         30  
  18.       7499 ALLEN                SALESMAN                 1600         30  
  19.       7844 TURNER               SALESMAN                 1500         30  

现在查询各部门各工种的总薪水,

  1. tony@ORA11GR2> select deptno, job, sum(sal) total_sal from emp  
  2.   2  group by deptno, job order by 1, 2;  
  3.   
  4.     DEPTNO JOB                 TOTAL_SAL  
  5. ---------- ------------------ ----------   
  6.         10 CLERK                    1300  
  7.         10 MANAGER                  2450  
  8.         10 PRESIDENT                5000  
  9.         20 ANALYST                  6000  
  10.         20 CLERK                    1900  
  11.         20 MANAGER                  2975  
  12.         30 CLERK                     950  
  13.         30 MANAGER                  2850  
  14.         30 SALESMAN                 5600  
但是这样不直观,如果能够把每个工种作为1列显示就会更一目了然.
这就是需要行转列。
在11g之前,需要一点技巧,利用decode函数才能完成这个目标。
  1. select deptno,  
  2.   sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT_SAL,  
  3.   sum(decode(job, 'MANAGER', sal, 0)) as MANAGER_SAL,  
  4.   sum(decode(job, 'ANALYST', sal, 0)) as ANALYST_SAL,  
  5.   sum(decode(job, 'CLERK', sal, 0)) as CLERK_SAL,  
  6.   sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN_SAL  
  7. from emp group by deptno order by 1;  

得到结果:

  1. DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL  CLERK_SAL SALESMAN_SAL  
  2. ------ ------------- ----------- ----------- ---------- ------------   
  3.     10          5000        2450           0       1300            0  
  4.     20             0        2975        6000       1900            0  
  5.     30             0        2850           0        950         5600  

如果要在变回前面的结果,需要用到笛卡尔乘积,一行变五行,然后利用decode。例如:

  1. with t as (  
  2. select deptno,  
  3.   sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT_SAL,  
  4.   sum(decode(job, 'MANAGER', sal, 0)) as MANAGER_SAL,  
  5.   sum(decode(job, 'ANALYST', sal, 0)) as ANALYST_SAL,  
  6.   sum(decode(job, 'CLERK', sal, 0)) as CLERK_SAL,  
  7.   sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN_SAL  
  8. from emp group by deptno  
  9. )  
  10. select deptno,  
  11. decode(lvl, 1, 'PRESIDENT', 2, 'MANAGER', 3, 'ANALYST',  
  12.             4, 'CLERK', 5, 'SALESMAN'as JOB,  
  13. decode(lvl, 1, PRESIDENT_SAL, 2, MANAGER_SAL, 3, ANALYST_SAL,  
  14.             4, CLERK_SAL, 5, SALESMAN_SAL) as TOTAL_SAL              
  15. from t, (select level lvl from dual connect by level <= 5)  
  16. order by 1, 2;  

得到结果:

  1. DEPTNO JOB                 TOTAL_SAL  
  2. ------ ------------------ ----------   
  3.     10 ANALYST                     0  
  4.     10 CLERK                    1300  
  5.     10 MANAGER                  2450  
  6.     10 PRESIDENT                5000  
  7.     10 SALESMAN                    0  
  8.     20 ANALYST                  6000  
  9.     20 CLERK                    1900  
  10.     20 MANAGER                  2975  
  11.     20 PRESIDENT                   0  
  12.     20 SALESMAN                    0  
  13.     30 ANALYST                     0  
  14.     30 CLERK                     950  
  15.     30 MANAGER                  2850  
  16.     30 PRESIDENT                   0  
  17.     30 SALESMAN                 5600  
11g之后,Oracle增加了pivotunpivot语句,可以很方便的完成这个转换。
pivot
先来看看pivot的语法
  1. SELECT ....  
  2. FROM <table-expr>  
  3.    PIVOT  
  4.      (  
  5.       aggregate-function(<column>)  
  6.       FOR <pivot-columnIN (<value1>, <value2>,..., <valuen>)  
  7.         ) AS <alias>  
  8. WHERE .....  
来看个例子:
  1. select * from  
  2.   (select deptno, job, sal from emp)  
  3. pivot(  
  4.   sum(sal) for job in (  
  5.     'PRESIDENT' as PRESIDENT_SAL,  
  6.     'MANAGER' as MANAGER_SAL,  
  7.     'ANALYST' as ANALYST_SAL,  
  8.     'CLERK' as CLERK_SAL,  
  9.     'SALESMAN' as SALESMAN_SAL  
  10.   )  
  11. order by 1;  

得到结果:

  1. DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL  CLERK_SAL SALESMAN_SAL  
  2. ------ ------------- ----------- ----------- ---------- ------------   
  3.     10          5000        2450                   1300  
  4.     20                      2975        6000       1900  
  5.     30                      2850                    950         5600  

实际上,oracle对pivot子句中出现的列以外的列做了一个隐式的group by.
现在,如果想要再结果中增加1列,显示部门的薪水总合,可以这么做,

  1. tony@ORA11GR2> select * from  
  2.   2    (select deptno, sum(sal) over (partition by deptno) SAL_TOTAL, job, sal from emp)  
  3.   3  pivot(  
  4.   4    sum(sal) as SAL_TOTAL for job in (  
  5.   5      'PRESIDENT' as PRESIDENT,  
  6.   6      'MANAGER' as MANAGER,  
  7.   7      'ANALYST' as ANALYST,  
  8.   8      'CLERK' as CLERK,  
  9.   9      'SALESMAN' as SALESMAN  
  10.  10    )  
  11.  11  ) order by 1;  
  12.   
  13.     DEPTNO  SAL_TOTAL PRESIDENT_SAL_TOTAL MANAGER_SAL_TOTAL ANALYST_SAL_TOTAL CLERK_SAL_TOTAL SALESMAN_SAL_TOTAL  
  14. ---------- ---------- ------------------- ----------------- ----------------- --------------- ------------------   
  15.         10       8750                5000              2450                              1300  
  16.         20      10875                                  2975              6000            1900  
  17.         30       9400                                  2850                               950               5600  

2点说明,
1)oracle对pivot子句中出现的列以外的列,也就是deptno和SAL_TOTAL做了隐式的group by.
      这里用了分析函数,对于每个deptno,SAL_TOTAL是唯一的,所以group by的结果还是3行。
2)oracle会拼接列名 = for字句中别名+聚合函数别名,比如'PRESIDENT'+'_'+'SAL_TOTAL'。

可以指定多个聚合函数,例如统计薪水总合和人数总合:

  1. tony@ORA11GR2> select * from  
  2.   2    (select deptno, job, sal from emp)  
  3.   3  pivot(  
  4.   4    sum(sal) as SAL_TOTAL, count(sal) as EMP_TOTAL for job in (  
  5.   5      'CLERK' as CLERK,  
  6.   6      'SALESMAN' as SALESMAN  
  7.   7    )  
  8.   8  ) order by 1;  
  9.   
  10.     DEPTNO CLERK_SAL_TOTAL CLERK_EMP_TOTAL SALESMAN_SAL_TOTAL SALESMAN_EMP_TOTAL  
  11. ---------- --------------- --------------- ------------------ ------------------   
  12.         10            1300               1                                     0  
  13.         20            1900               2                                     0  
  14.         30             950               1               5600                  4  

for子句可以指定多列
为此,先给emp表追加1列rank,取值为'A','B',

  1. tony@ORA11GR2> alter table emp add (rank varchar2(1) default('A'));  
  2.   
  3. 表已更改。  
  4.   
  5. tony@ORA11GR2> update emp set rank=decode(mod(rownum, 2), 0, 'B', rank);  
  6.   
  7. 已更新14行。  
  8.   
  9. tony@ORA11GR2> select deptno, job, rank, count(sal) as EMP_TOTAL from emp  
  10.   2  group by deptno, job, rank order by 1, 2;  
  11.   
  12.     DEPTNO JOB                RA  EMP_TOTAL  
  13. ---------- ------------------ -- ----------   
  14.         10 CLERK              B           1  
  15.         10 MANAGER            A           1  
  16.         10 PRESIDENT          A           1  
  17.         20 ANALYST            A           1  
  18.         20 ANALYST            B           1  
  19.         20 CLERK              A           2  
  20.         20 MANAGER            B           1  
  21.         30 CLERK              B           1  
  22.         30 MANAGER            B           1  
  23.         30 SALESMAN           A           2  
  24.         30 SALESMAN           B           2  

现在,想统计SALESMAN和CLERK的员工中,rank A和rank B各自的人数。

  1. tony@ORA11GR2> select * from  
  2.   2    (select deptno, job, rank from emp)  
  3.   3  pivot(  
  4.   4    count(rank) as EMP_TOTAL for (job, rank) in (  
  5.   5      ('SALESMAN''A'as SALESMAN_A,  
  6.   6      ('SALESMAN''B'as SALESMAN_B,  
  7.   7      ('CLERK''A'as CLERK_A,  
  8.   8      ('CLERK''B'as CLERK_B  
  9.   9    )  
  10.  10  ) order by 1;  
  11.   
  12.     DEPTNO SALESMAN_A_EMP_TOTAL SALESMAN_B_EMP_TOTAL CLERK_A_EMP_TOTAL CLERK_B_EMP_TOTAL  
  13. ---------- -------------------- -------------------- ----------------- -----------------   
  14.         10                    0                    0                 0                 1  
  15.         20                    0                    0                 2                 0  
  16.         30                    2                    2                 0                 1  
unpivot
现在来看看unpivot的用法,
unpivot的语法
  1. SELECT ....  
  2. FROM <table-expr>  
  3.    UNPIVOT [include nulls|exclude nulls]  
  4.      (  
  5.       (<column>)  
  6.       FOR <pivot-columnIN (<value1>, <value2>,..., <valuen>)  
  7.         ) AS <alias>  
  8. WHERE .....  
例如,有下面的的表格,
  1. DEPTNO  CLERK_SAL SALESMAN_SAL  
  2. ------ ---------- ------------   
  3.     10       1300  
  4.     20       1900  
  5.     30        950         5600  

用unpivot语句来做列到行的转换,

  1. tony@ORA11GR2> with t as (  
  2.   2  select * from  
  3.   3    (select deptno, job, sal from emp)  
  4.   4  pivot(  
  5.   5    sum(sal) for job in (  
  6.   6      'CLERK' as CLERK_SAL,  
  7.   7      'SALESMAN' as SALESMAN_SAL  
  8.   8    )  
  9.   9  )  
  10.  10  )  
  11.  11  select * from t  
  12.  12  unpivot(  
  13.  13    SAL_TOTAL for JOB in (  
  14.  14      CLERK_SAL as 'CLERK',  
  15.  15      SALESMAN_SAL as 'SALESMAN'  
  16.  16    )  
  17.  17  ) order by 1,2;  
  18.   
  19.     DEPTNO JOB               SAL_TOTAL  
  20. ---------- ---------------- ----------   
  21.         10 CLERK                  1300  
  22.         20 CLERK                  1900  
  23.         30 CLERK                   950  
  24.         30 SALESMAN               5600  

如果加上include nulls子句

  1. tony@ORA11GR2> with t as (  
  2.   2  select * from  
  3.   3    (select deptno, job, sal from emp)  
  4.   4  pivot(  
  5.   5    sum(sal) for job in (  
  6.   6      'CLERK' as CLERK_SAL,  
  7.   7      'SALESMAN' as SALESMAN_SAL  
  8.   8    )  
  9.   9  )  
  10.  10  )  
  11.  11  select * from t  
  12.  12  unpivot include nulls(  
  13.  13    SAL_TOTAL for JOB in (  
  14.  14      CLERK_SAL as 'CLERK',  
  15.  15      SALESMAN_SAL as 'SALESMAN'  
  16.  16    )  
  17.  17  ) order by 1,2;  
  18.   
  19.     DEPTNO JOB               SAL_TOTAL  
  20. ---------- ---------------- ----------   
  21.         10 CLERK                  1300  
  22.         10 SALESMAN  
  23.         20 CLERK                  1900  
  24.         20 SALESMAN  
  25.         30 CLERK                   950  
  26.         30 SALESMAN               5600  
可以指定多个pivot-column,例如对于下面的表格
  1. DEPTNO CLERK_SAL_TOTAL CLERK_EMP_TOTAL SALESMAN_SAL_TOTAL SALESMAN_EMP_TOTAL  
  2. ------ --------------- --------------- ------------------ ------------------   
  3.     30             950               1               5600                  4  
  4.     20            1900               2                                     0  
  5.     10            1300               1                                     0  

转换为行以后,有2列数据分别显示薪水总合和人数总和。也就是需要对2列同时进行转换。

  1. tony@ORA11GR2> with t as (  
  2.   2  select * from  
  3.   3    (select deptno, job, sal from emp)  
  4.   4  pivot(  
  5.   5    sum(sal) as SAL_TOTAL, count(sal) as EMP_TOTAL for job in (  
  6.   6      'CLERK' as CLERK,  
  7.   7      'SALESMAN' as SALESMAN  
  8.   8    )  
  9.   9  )  
  10.  10  )  
  11.  11  select * from t  
  12.  12  unpivot include nulls(  
  13.  13    (SAL_TOTAL, EMP_TOTAL) for JOB in (  
  14.  14      (CLERK_SAL_TOTAL, CLERK_EMP_TOTAL) as 'CLERK',  
  15.  15      (SALESMAN_SAL_TOTAL, SALESMAN_EMP_TOTAL) as 'SALESMAN'  
  16.  16    )  
  17.  17  ) order by 1,2;  
  18.   
  19.     DEPTNO JOB               SAL_TOTAL  EMP_TOTAL  
  20. ---------- ---------------- ---------- ----------   
  21.         10 CLERK                  1300          1  
  22.         10 SALESMAN                             0  
  23.         20 CLERK                  1900          2  
  24.         20 SALESMAN                             0  
  25.         30 CLERK                   950          1  
  26.         30 SALESMAN               5600          4  

关于ANY子句

pivot的一个不便之处是需要在IN子句中指定所有取值,能不能自动完成呢?
比如for job in (select distinct(job) from emp),可惜oracle不支持这么做。
不过oracle支持通过这种方式返回XML格式数据,例如,

  1. tony@ORA11GR2> select * from  
  2.   2    (select deptno, job, sal from emp)  
  3.   3  pivot XML(  
  4.   4    sum(sal) for job in (ANY)  
  5.   5  ) order by 1;  
  6.   
  7.     DEPTNO JOB_XML  
  8. ---------- --------------------------------------------------------------------------------   
  9.         10 <PivotSet><item><column name = "JOB">CLERK</column><column name = "SUM(SAL)">130  
  10.            0</column></item><item><column name = "JOB">MANAGER</column><column name = "SUM(  
  11.            SAL)">2450</column></item><item><column name = "JOB">PRESIDENT</column><column n  
  12.            ame = "SUM(SAL)">5000</column></item></PivotSet>  
  13.   
  14.         20 <PivotSet><item><column name = "JOB">ANALYST</column><column name = "SUM(SAL)">6  
  15.            000</column></item><item><column name = "JOB">CLERK</column><column name = "SUM(  
  16.            SAL)">1900</column></item><item><column name = "JOB">MANAGER</column><column nam  
  17.            e = "SUM(SAL)">2975</column></item></PivotSet>  
  18.   
  19.         30 <PivotSet><item><column name = "JOB">CLERK</column><column name = "SUM(SAL)">950  
  20.            </column></item><item><column name = "JOB">MANAGER</column><column name = "SUM(S  
  21.            AL)">2850</column></item><item><column name = "JOB">SALESMAN</column><column nam  
  22.            e = "SUM(SAL)">5600</column></item></PivotSet>  

*可以把上面的ANY换成select distinct(job) from emp

linux
相关资讯       Oracle行列转换 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款