在进行报表开发时，很多时候会遇到行列转换操作，很对开发人员针对于SQL级别行列转换操作一直不甚理解，今天正好抽空对其进行了一些简单的总结。这里主要列举3种可以实现SQL行列转换的方法，包括通用SQL解法以及Oracle支持解法。
一、测试数据
测试表依旧采用Oracle经典的scott模式下的dept表和emp表，结构如下：
DEPT：
|
create table DEPT ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13) ) -- Create/Recreate primary, unique and foreign key constraints alter table DEPT add constraint PK_DEPT primary key (DEPTNO) using index; |
|
SQL> select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON |
EMP:
|
create table EMP ( EMPNO NUMBER(4) not null, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2), SEX VARCHAR2(2) default '男' ) -- Create/Recreate primary, unique and foreign key constraints alter table EMP add constraint PK_EMP primary key (EMPNO) using index; alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO); |
|
SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SEX ----- ---------- --------- ----- ----------- --------- --------- ------ --- 7369 SMITH CLERK 7902 1980/12/17 800.00 20 男 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30 女 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30 女 7566 JONES MANAGER 7839 1981/4/2 2975.00 20 女 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30 女 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 女 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10 女 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20 男 7839 KING PRESIDENT 1981/11/17 5000.00 10 女 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 女 7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 男 7900 JAMES CLERK 7698 1981/12/3 950.00 30 女 7902 FORD ANALYST 7566 1981/12/3 3000.00 20 女 7934 MILLER CLERK 7782 1982/1/23 1300.00 10 男 14 rows selected |
二、行列转换方法
需求：查询每个部门中不同职位总工资。
1、最传统的SQL解法如下：
|
SQL> select d.dname, e.job, sum(sal) as sum_sal 2 from emp e, dept d 3 where e.deptno = d.deptno 4 group by d.dname, e.job; DNAME JOB SUM_SAL -------------- --------- ---------- SALES MANAGER 2850 SALES CLERK 950 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING CLERK 1300 RESEARCH MANAGER 2975 SALES SALESMAN 5600 RESEARCH ANALYST 6000 RESEARCH CLERK 1900 9 rows selected |
以上方法确实得到了不同部门不同职位的工资总和，可是我们看到每一个部门出现了多行信息，这并不是我们想要的结果，我们更希望每个部门显示为1行。
2、使用子查询实现行列转换：
|
SQL> select e.deptno, 2 (select sum(sal) from emp where emp.deptno = e.deptno and job='ANALYST') as analyst_job, 3 (select sum(sal) from emp where emp.deptno=e.deptno and job='CLERK') as clerk_job, 4 (select sum(sal) from emp where emp.deptno=e.deptno and job='MANAGER') as manager_job, 5 (select sum(sal) from emp where emp.deptno=e.deptno and job='PRESIDENT') as persident_job, 6 (select sum(sal) from emp where emp.deptno=e.deptno and job='SALESMAN') as salesman_job 7 from emp e 8 group by e.deptno; DEPTNO ANALYST_JOB CLERK_JOB MANAGER_JOB PERSIDENT_JOB SALESMAN_JOB ------ ----------- ---------- ----------- ------------- ------------ 30 950 2850 5600 20 6000 1900 2975 10 1300 2450 5000 |
3、尝试更改SQL如以下形式达到目的：
|
SQL> select DNAME, 2 SUM(MANAGER_JOB) AS SUM_MANAGER_SAL, 3 SUM(PRESIDENT_JOB) AS SUM_PRESIDENT_SAL, 4 SUM(CLERK_JOB) AS SUM_CLERK_SAL, 5 SUM(ANALYST_JOB) AS SUM_ANALYST_SAL, 6 SUM(SALESMAN_JOB) AS SUM_SALESMAN_SAL 7 from (select d.dname, 8 (select sum(sal) 9 from emp 10 where emp.empno = e.empno 11 and emp.job = 'MANAGER') as MANAGER_JOB, 12 (select sum(sal) 13 from emp 14 where emp.empno = e.empno 15 and emp.job = 'PRESIDENT') as PRESIDENT_JOB, 16 (select sum(sal) 17 from emp 18 where emp.empno = e.empno 19 and emp.job = 'CLERK') as CLERK_JOB, 20 (select sum(sal) 21 from emp 22 where emp.empno = e.empno 23 and emp.job = 'ANALYST') as ANALYST_JOB, 24 (select sum(sal) 25 from emp 26 where emp.empno = e.empno 27 and emp.job = 'SALESMAN') as SALESMAN_JOB 28 from emp e, dept d 29 where e.deptno = d.deptno) temp 30 group by temp.dname 31 order by temp.dname; DNAME SUM_MANAGER_SAL SUM_PRESIDENT_SAL SUM_CLERK_SAL SUM_ANALYST_SAL SUM_SALESMAN_SAL -------------- --------------- ----------------- ------------- --------------- ---------------- ACCOUNTING 2450 5000 1300 RESEARCH 2975 1900 6000 SALES 2850 950 5600 |
通过查询结果可以看到，这里我们已经实现了行列转换，同时这个结果也符合我们日常阅读习惯。但是代码过于复杂，执行计划回表次数明显很会很多。这里的代码基本符合任何一种支持SQL的数据库基本语法，属于通用模式，那么有没有简单一点的方法呢？答案是肯定的！
4、基于Oracle decode()函数的行列转换：
|
SQL> select d.dname, 2 sum(decode(job, 'MANAGER', sal, 0)) as SUM_MANAGER_SAL, 3 sum(decode(job, 'PRESIDENT', sal, 0)) as SUM_PRESIDENT_SAL, 4 sum(decode(job, 'CLERK', sal, 0)) as SUM_CLERK_SAL, 5 sum(decode(job, 'ANALYST', sal, 0)) as SUM_ANALYST_SAL, 6 sum(decode(job, 'SALESMAN', sal, 0)) as SUM_SALESMAN_SAL 7 from emp e, dept d 8 where e.deptno = d.deptno 9 group by d.dname; DNAME SUM_MANAGER_SAL SUM_PRESIDENT_SAL SUM_CLERK_SAL SUM_ANALYST_SAL SUM_SALESMAN_SAL -------------- --------------- ----------------- ------------- --------------- ---------------- ACCOUNTING 2450 5000 1300 0 0 RESEARCH 2975 0 1900 6000 0 SALES 2850 0 950 0 5600 |
根据上面执行结果，我们可以看到通过Oracle数据库的decode()函数达到了进行行列装换的目的，而且明显针对于前一种行列转换操作效率提升不少。
5、通用行列转换可以通过case when ... then ...else ... end实现：
|
SQL> select d.dname, 2 sum(case 3 when e.job = 'ANALYST' then 4 sal 5 else 6 0 7 end) as ANALYST_JOB, 8 sum(case 9 when e.job = 'CLERK' then 10 sal 11 else 12 0 13 end) as CLERK_JOB, 14 sum(case 15 when e.job = 'MANAGER' then 16 sal 17 else 18 0 19 end) as MANAGER_JOB, 20 sum(case 21 when e.job = 'PRESIDENT' then 22 sal 23 else 24 0 25 end) as PRESIDENT_JOB, 26 sum(case 27 when e.job = 'SALESMAN' then 28 sal 29 else 30 0 31 end) as SALESMAN_JOB 32 from emp e, dept d 33 where e.deptno = d.deptno 34 group by d.dname; DNAME ANALYST_JOB CLERK_JOB MANAGER_JOB PRESIDENT_JOB SALESMAN_JOB -------------- ----------- ---------- ----------- ------------- ------------ ACCOUNTING 0 1300 2450 5000 0 RESEARCH 6000 1900 2975 0 0 SALES 0 950 2850 0 5600 |
6、在Oracle 11g中引入了pivot和unpivot函数功能，专门用来进行行列转换，pivot函数语法如下：
|
select *|列 [别名],... from 子查询 pivot( 统计函数(列)s for 转换列名称 in( 内容1 [[as] 别名], 内容2 [[as] 别名], ... ... 内容n [[as] 别名] ) ) [where 条件(s)] [group by 分组字段1,分组字段2,... ...] [having 过滤条件(s)] [order by 排序字段 asc|desc] |
使用pivot函数实现行列转换：
|
SQL> select * 2 from (select d.dname, e.job, e.sal 3 from emp e, dept d 4 where e.deptno = d.deptno) pivot(sum(sal) for job in('ANALYST' AS 5 ANALYST_JOB, 6 'CLERK' AS 7 CLERK_JOB, 8 'MANAGER' AS 9 MANAGER_JOB, 10 'PRESIDENT' AS 11 PRESIDENT_JOB, 12 'SALESMAN' AS 13 SALESMAN_JOB)) 14 order by dname; DNAME ANALYST_JOB CLERK_JOB MANAGER_JOB PRESIDENT_JOB SALESMAN_JOB -------------- ----------- ---------- ----------- ------------- ------------ ACCOUNTING 1300 2450 5000 RESEARCH 6000 1900 2975 SALES 950 2850 5600 |
以上SQL使用pivot函数实现了行列转换操作。至此，对于行列转换操作我们使用3种方法实现。
以下是pivot函数实现更复杂一些SQL的例子：
6.1、查询每个部门的总工资，每个部门不同职位的总工资和每个部门的最高工资和最低工资：
|
SQL> select * 2 from (select deptno, 3 job, 4 sal, 5 sum(sal) over(partition by deptno) as sum_sal, 6 max(sal) over(partition by deptno) as max_sal, 7 min(sal) over(partition by deptno) as min_sal 8 from emp) pivot(sum(sal) for job in('ANALYST' AS ANALYST_JOB, 9 'CLERK' AS CLERK_JOB, 10 'MANAGER' AS MANAGER_JOB, 11 'PRESIDENT' AS PRESIDENT_JOB, 12 'SALESMAN' AS SALESMAN_JOB)) 13 order by deptno; DEPTNO SUM_SAL MAX_SAL MIN_SAL ANALYST_JOB CLERK_JOB MANAGER_JOB PRESIDENT_JOB SALESMAN_JOB ------ ---------- ---------- ---------- ----------- ---------- ----------- ------------- ------------ 10 8750 5000 1300 1300 2450 5000 20 10875 3000 800 6000 1900 2975 30 9400 2850 950 950 2850 5600 |
6.2、查询每个部门不同职位总工资以及每个部门不同职位最高工资：
|
SQL> select * 2 from (select deptno, job, sal from emp) 3 pivot(sum(sal) as sum_sal, max(sal) as sum_max 4 for job in('ANALYST' AS ANA_JOB, 5 'CLERK' AS CLE_JOB, 6 'MANAGER' AS MAN_JOB/*, 7 'PRESIDENT' AS PRE_JOB, 8 'SALESMAN' AS SAL_JOB*/) 9 ) 10 order by deptno; DEPTNO ANA_JOB_SUM_SAL ANA_JOB_SUM_MAX CLE_JOB_SUM_SAL CLE_JOB_SUM_MAX MAN_JOB_SUM_SAL MAN_JOB_SUM_MAX ------ --------------- --------------- --------------- --------------- --------------- --------------- 10 1300 1300 2450 2450 20 6000 3000 1900 1100 2975 2975 30 950 950 2850 2850 |
6.3、统计每个部门不同职位总工资和不同性别的最高工资：
|
SQL> select * 2 from (select deptno, job, sex, sal from emp) 3 pivot(sum(sal) as sum_sal, max(sal) as sum_max 4 for(job, sex) in(('MANAGER', '男') AS MANAGER_JOB_MAN, 5 ('MANAGER', '女') AS MANAGER_JOB_WOMAN/*, 6 ('CLERK', '男') AS CLERK_JOB_MAN, 7 ('CLERK', '女') AS CLERK_JOB_WOMAN, 8 ('ANALYST', '男') AS ANALYST_JOB_MAN, 9 ('ANALYST', '女') AS ANALYST_JOB_WOMAN, 10 ('PRESIDENT', '男') AS PRESIDENT_JOB_MAN, 11 ('PRESIDENT', '女') AS PRESIDENT_JOB_WOMAN, 12 ('SALESMAN', '男') AS SALESMAN_JOB_MAN, 13 ('SALESMAN', '女') AS SALESMAN_JOB_WOMAN*/)) 14 order by deptno; DEPTNO MANAGER_JOB_MAN_SUM_SAL MANAGER_JOB_MAN_SUM_MAX MANAGER_JOB_WOMAN_SUM_SAL MANAGER_JOB_WOMAN_SUM_MAX ------ ----------------------- ----------------------- ------------------------- ------------------------- 10 2450 2450 20 2975 2975 30 2850 2850 |
6.4、如果在pivot中增加xml显示，可以利用any设置所要操作的所有数据，目前any仅能使用在xml显示中：
|
SQL> select * 2 from (select deptno, job, sal from emp) pivot xml(sum(sal) for job in(any)) 3 order by deptno; |
6.5、pivot功能是将行转换为列；同样unpivot功能进行列转行操作，操作语法如下：
|
select *|列 [别名],... from 子查询 unpivot [include nulls|exclude nulls(默认)]( 统计函数(列)s for 转换列名称 in( 内容1 [[as] 别名], 内容2 [[as] 别名], ... ... 内容n [[as] 别名] ) ) [where 条件(s)] [group by 分组字段1,分组字段2,... ...] [having 过滤条件(s)] [order by 排序字段 asc|desc] |
如把步骤2中行转列的SQL结果再转回行：
|
SQL> with temp as 2 (select * 3 from (select d.dname, e.job, e.sal 4 from emp e, dept d 5 where e.deptno = d.deptno) pivot(sum(sal) for job in('ANALYST' AS 6 ANALYST_JOB, 7 'CLERK' AS 8 CLERK_JOB, 9 'MANAGER' AS 10 MANAGER_JOB, 11 'PRESIDENT' AS 12 PRESIDENT_JOB, 13 'SALESMAN' AS 14 SALESMAN_JOB)) 15 order by dname) 16 select * 17 from temp unpivot(sum_sal for job in(ANALYST_JOB as 'ANALYST', 18 CLERK_JOB as 'CLERK', 19 MANAGER_JOB as 'MANAGER', 20 PRESIDENT_JOB as 'PRESIDENT', 21 SALESMAN_JOB as 'SALESMAN')); DNAME JOB SUM_SAL -------------- --------- ---------- ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 RESEARCH ANALYST 6000 RESEARCH CLERK 1900 RESEARCH MANAGER 2975 SALES CLERK 950 SALES MANAGER 2850 SALES SALESMAN 5600 9 rows selected |
从上述结果可以看到，这里的结果已经将列转换为行了，但是不包含nulls值得行，如果想包含nulls值的行业显示出来需要显示的生命include nulls关键字：
|
SQL> with temp as 2 (select * 3 from (select d.dname, e.job, e.sal 4 from emp e, dept d 5 where e.deptno = d.deptno) pivot(sum(sal) for job in('ANALYST' AS 6 ANALYST_JOB, 7 'CLERK' AS 8 CLERK_JOB, 9 'MANAGER' AS 10 MANAGER_JOB, 11 'PRESIDENT' AS 12 PRESIDENT_JOB, 13 'SALESMAN' AS 14 SALESMAN_JOB)) 15 order by dname) 16 select * 17 from temp unpivot include nulls(sum_sal for job in(ANALYST_JOB as 18 'ANALYST', 19 CLERK_JOB as 'CLERK', 20 MANAGER_JOB as 21 'MANAGER', 22 PRESIDENT_JOB as 23 'PRESIDENT', 24 SALESMAN_JOB as 25 'SALESMAN')); DNAME JOB SUM_SAL -------------- --------- ---------- ACCOUNTING ANALYST ACCOUNTING CLERK 1300 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING SALESMAN RESEARCH ANALYST 6000 RESEARCH CLERK 1900 RESEARCH MANAGER 2975 RESEARCH PRESIDENT RESEARCH SALESMAN SALES ANALYST SALES CLERK 950 SALES MANAGER 2850 SALES PRESIDENT SALES SALESMAN 5600 15 rows selected |