11g之后，oracle增加了pivot和unpivot语句，可以很方便的完成行列转换。
先来看看pivot的语法是

点击(此处)折叠或打开

SELECT ....
FROM <table-expr>
PIVOT
(
aggregate-function(<column>)
FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
) AS <alias>
WHERE .....

来看个例子，emp表中数据如下：

点击(此处)折叠或打开

SQL> select empno,ename,job,sal,deptno from emp order by deptno,job;
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7934 MILLER CLERK 1300 10
7782 CLARK MANAGER 2450 10
7839 KING PRESIDENT 5000 10
7902 FORD ANALYST 3000 20
7369 SMITH CLERK 800 20
7566 JONES MANAGER 2975 20
7900 JAMES CLERK 950 30
7698 BLAKE MANAGER 2850 30
7844 TURNER SALESMAN 1500 30
7521 WARD SALESMAN 1250 30
7499 ALLEN SALESMAN 1600 30
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7654 MARTIN SALESMAN 1250 30
已选择12行。

现在查询各部门各工种的总薪水，如下：

点击(此处)折叠或打开

SQL> select deptno,job,sum(sal) from emp group by deptno,job order by 1,2;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 CLERK 800
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
已选择9行。

现在需要将每个工种作为一列显示就更一目了然，这就需要进行行列转换了，先看11G之前，利用decode函数来实现行列转换

点击(此处)折叠或打开

SQL> edit
已写入 file afiedt.buf
1 select deptno,
2 sum(decode(job,'CLERK',sal,0)) as CLERK_sal,
3 sum(decode(job,'MANAGER',sal,0)) as MANAGER_sal,
4 sum(decode(job,'PRESIDENT',sal,0)) as PRESIDENT_sal,
5 sum(decode(job,'ANALYST',sal,0)) as ANALYST_sal,
6 sum(decode(job,'SALESMAN',sal,0)) as SALESMAN_sal
7* from emp group by deptno order by 1
SQL> /
DEPTNO CLERK_SAL MANAGER_SAL PRESIDENT_SAL ANALYST_SAL SALESMAN_SAL
---------- ---------- ----------- ------------- ----------- ------------
10 1300 2450 5000 0 0
20 800 2975 0 3000 0
30 950 2850 0 0 5600
SQL>

再看看11G新特性pivot实现行列转换，如下：

点击(此处)折叠或打开

SQL> edit;
已写入 file afiedt.buf
1 select * from(
2 select deptno,job,sal from emp
3 )
4 pivot(
5 sum(sal) for job in(
6 'PRESIDENT' as PRESIDENT_SAL,
7 'MANAGER' as MANAGER_SAL,
8 'ANALYST' as ANALYST_SAL,
9 'CLERK' as CLERK_SAL,
10 'SALESMAN' as SALESMAN_SAL
11 )
12* ) order by 1
SQL> /
DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL CLERK_SAL SALESMAN_SAL
---------- ------------- ----------- ----------- ---------- ------------
10 5000 2450 1300
20 2975 3000 800
30 2850 950 5600
SQL>

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

点击(此处)折叠或打开

SQL> edit
已写入 file afiedt.buf
1 select * from(
2 select deptno,job,sal,sum(sal) over(partition by deptno) sal_total from emp
3 )
4 pivot(
5 sum(sal) as total_sal for job in(
6 'PRESIDENT' as P,
7 'MANAGER' as M,
8 'ANALYST' as A,
9 'CLERK' as C,
10 'SALESMAN' as S
11 )
12* ) order by 1
SQL> /
DEPTNO SAL_TOTAL P_TOTAL_SAL M_TOTAL_SAL A_TOTAL_SAL C_TOTAL_SAL S_TOTAL_SAL
---------- ---------- ----------- ----------- ----------- ----------- -----------
10 8750 5000 2450 1300
20 6775 2975 3000 800
30 9400 2850 950 5600
SQL>

说明：
1）oracle对pivot子句中出现的列以外的列，也就是deptno和SAL_TOTAL做了隐式的group by.这里用了分析函数，对于每个deptno，SAL_TOTAL是唯一的，所以group by的结果还是3行。
2）oracle会拼接列名 = for字句中别名+聚合函数别名，比如'P'+'_'+'SAL_TOTAL'
可以指定多个聚合函数，如统计薪水总合与人数总合，如下：

点击(此处)折叠或打开

SQL> edit;
已写入 file afiedt.buf
1 select * from(
2 select deptno,job,sal from emp
3 )
4 pivot(
5 sum(sal) as sal_total,count(sal) as emp_total for job in(
6 'PRESIDENT' as PRESIDENT,
7 'MANAGER' as MANAGER
8 )
9* ) order by 1
SQL> /
DEPTNO PRESIDENT_SAL_TOTAL PRESIDENT_EMP_TOTAL MANAGER_SAL_TOTAL MANAGER_EMP_TOTAL
---------- ------------------- ------------------- ----------------- -----------------
10 5000 1 2450 1
20 0 2975 1
30 0 2850 1

for子句可以指定多列，如想统计salesman和clerk员工，其mgr为7902和7698各自的人数，如下：

点击(此处)折叠或打开

SQL> edit;
已写入 file afiedt.buf
1 select * from(
2 select deptno,job,mgr from emp
3 )
4 pivot(
5 count(mgr) as emp_total for(job,mgr) in(
6 ('SALESMAN', '7902') as SALESMAN_7902,
7 ('SALESMAN', '7698') as SALESMAN_7698,
8 ('CLERK', '7902') as CLERK_7902,
9 ('CLERK', '7698') as CLERK_7698
10 )
11* )
SQL> /
DEPTNO SALESMAN_7902_EMP_TOTAL SALESMAN_7698_EMP_TOTAL CLERK_7902_EMP_TOTAL CLERK_7698_EMP_TOTAL
---------- ----------------------- ----------------------- -------------------- --------------------
30 0 4 0 1
20 0 0 1 0
10 0 0 0 0

ITPub博客

pivot、unpivot实现oracle行列转换

最新文章