通用的行列转换的方法

千家信息网最后更新 2025年01月19日通用的行列转换的方法在工作中经常需要用到行列转换进行聚合统计之类的事儿，现在就说一个最简单最通用的方法， 适应sum()/max()/min()等函数结合decode来实现行列转换， 拿oracle中典型的scott.emp表为例，

点击(此处)折叠或打开

1. SQL> SELECT * FROM SCOTT.EMP ;
   
2. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
   
3. ----- ---------- --------- ----- ----------- --------- --------- ------
   
4. 7369 SMITH CLERK 7902 1980/12/17 800.00 20
   
5. 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
   
6. 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
   
7. 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
   
8. 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
   
9. 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
   
10. 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
    
11. 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
    
12. 7839 KING PRESIDENT 1981/11/17 5000.00 10
    
13. 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
    
14. 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
    
15. 7900 JAMES CLERK 7698 1981/12/3 950.00 30
    
16. 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
    
17. 7934 MILLER CLERK 7782 1982/1/23 1300.00 10
    
18. 14 rows selected

现在如果想要按照部门统计一下， 各个manager下的员工的平均薪水（或者最高薪水、最低、合计薪水），这样就使用到了行列转换， 可做如下操作来实现：

点击(此处)折叠或打开

1. SQL> select deptno ,
   
2. 2 avg(decode(mgr,'7566' ,sal,0)) jones ,
   
3. 3 avg(decode(mgr,'7698' , sal,0)) BLAKE ,
   
4. 4 avg(decode(mgr,'7782' ,sal,0)) CLARK ,
   
5. 5 avg(decode(mgr,'7788' , sal,0)) SCOTT ,
   
6. 6 avg(decode(mgr,'7839' , sal,0)) KING ,
   
7. 7 avg(decode(mgr,'7902' , sal,0)) FORD
   
8. 8 from scott.emp
   
9. 9 group by deptno
   
10. 10 ;
    
11. DEPTNO JONES BLAKE CLARK SCOTT KING FORD
    
12. ------ ---------- ---------- ---------- ---------- ---------- ----------
    
13. 30 0 1091.66666 0 0 475 0
    
14. 20 1200 0 0 220 595 160
    
15. 10 0 0 433.333333 0 816.666666 0

可以看到很容易的实现了数据的统计。
当然行列的转换的方法还有其他几种， 如使用case when ，或者使用wmsys.wm_concat函数都都可以实现。 不过上面的方法算是最容易理解了的吧。