首先准备如下表格
- tony@ORA11GR2> select empno,ename,job,sal,deptno from emp
- 2 order by deptno,job;
- EMPNO ENAME JOB SAL DEPTNO
- ---------- -------------------- ------------------ ---------- ----------
- 7934 MILLER CLERK 1300 10
- 7782 CLARK MANAGER 2450 10
- 7839 KING PRESIDENT 5000 10
- 7788 SCOTT ANALYST 3000 20
- 7902 FORD ANALYST 3000 20
- 7876 ADAMS CLERK 1100 20
- 7369 SMITH CLERK 800 20
- 7566 JONES MANAGER 2975 20
- 7900 JAMES CLERK 950 30
- 7698 BLAKE MANAGER 2850 30
- 7654 MARTIN SALESMAN 1250 30
- 7521 WARD SALESMAN 1250 30
- 7499 ALLEN SALESMAN 1600 30
- 7844 TURNER SALESMAN 1500 30
现在查询各部门各工种的总薪水,
- tony@ORA11GR2> select deptno, job, sum(sal) total_sal from emp
- 2 group by deptno, job order by 1, 2;
- DEPTNO JOB TOTAL_SAL
- ---------- ------------------ ----------
- 10 CLERK 1300
- 10 MANAGER 2450
- 10 PRESIDENT 5000
- 20 ANALYST 6000
- 20 CLERK 1900
- 20 MANAGER 2975
- 30 CLERK 950
- 30 MANAGER 2850
- 30 SALESMAN 5600
这就是需要行转列。
在11g之前,需要一点技巧,利用decode函数才能完成这个目标。
- select deptno,
- sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT_SAL,
- sum(decode(job, 'MANAGER', sal, 0)) as MANAGER_SAL,
- sum(decode(job, 'ANALYST', sal, 0)) as ANALYST_SAL,
- sum(decode(job, 'CLERK', sal, 0)) as CLERK_SAL,
- sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN_SAL
- from emp group by deptno order by 1;
得到结果:
- DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL CLERK_SAL SALESMAN_SAL
- ------ ------------- ----------- ----------- ---------- ------------
- 10 5000 2450 0 1300 0
- 20 0 2975 6000 1900 0
- 30 0 2850 0 950 5600
如果要在变回前面的结果,需要用到笛卡尔乘积,一行变五行,然后利用decode。例如:
- with t as (
- select deptno,
- sum(decode(job, 'PRESIDENT', sal, 0)) as PRESIDENT_SAL,
- sum(decode(job, 'MANAGER', sal, 0)) as MANAGER_SAL,
- sum(decode(job, 'ANALYST', sal, 0)) as ANALYST_SAL,
- sum(decode(job, 'CLERK', sal, 0)) as CLERK_SAL,
- sum(decode(job, 'SALESMAN', sal, 0)) as SALESMAN_SAL
- from emp group by deptno
- )
- select deptno,
- decode(lvl, 1, 'PRESIDENT', 2, 'MANAGER', 3, 'ANALYST',
- 4, 'CLERK', 5, 'SALESMAN') as JOB,
- decode(lvl, 1, PRESIDENT_SAL, 2, MANAGER_SAL, 3, ANALYST_SAL,
- 4, CLERK_SAL, 5, SALESMAN_SAL) as TOTAL_SAL
- from t, (select level lvl from dual connect by level <= 5)
- order by 1, 2;
得到结果:
- DEPTNO JOB TOTAL_SAL
- ------ ------------------ ----------
- 10 ANALYST 0
- 10 CLERK 1300
- 10 MANAGER 2450
- 10 PRESIDENT 5000
- 10 SALESMAN 0
- 20 ANALYST 6000
- 20 CLERK 1900
- 20 MANAGER 2975
- 20 PRESIDENT 0
- 20 SALESMAN 0
- 30 ANALYST 0
- 30 CLERK 950
- 30 MANAGER 2850
- 30 PRESIDENT 0
- 30 SALESMAN 5600
pivot
先来看看pivot的语法是
- SELECT ....
- FROM <table-expr>
- PIVOT
- (
- aggregate-function(<column>)
- FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
- ) AS <alias>
- WHERE .....
- select * from
- (select deptno, job, sal from emp)
- pivot(
- sum(sal) for job in (
- 'PRESIDENT' as PRESIDENT_SAL,
- 'MANAGER' as MANAGER_SAL,
- 'ANALYST' as ANALYST_SAL,
- 'CLERK' as CLERK_SAL,
- 'SALESMAN' as SALESMAN_SAL
- )
- ) order by 1;
得到结果:
- DEPTNO PRESIDENT_SAL MANAGER_SAL ANALYST_SAL CLERK_SAL SALESMAN_SAL
- ------ ------------- ----------- ----------- ---------- ------------
- 10 5000 2450 1300
- 20 2975 6000 1900
- 30 2850 950 5600
实际上,oracle对pivot子句中出现的列以外的列做了一个隐式的group by.
现在,如果想要再结果中增加1列,显示部门的薪水总合,可以这么做,
- tony@ORA11GR2> select * from
- 2 (select deptno, sum(sal) over (partition by deptno) SAL_TOTAL, job, sal from emp)
- 3 pivot(
- 4 sum(sal) as SAL_TOTAL for job in (
- 5 'PRESIDENT' as PRESIDENT,
- 6 'MANAGER' as MANAGER,
- 7 'ANALYST' as ANALYST,
- 8 'CLERK' as CLERK,
- 9 'SALESMAN' as SALESMAN
- 10 )
- 11 ) order by 1;
- DEPTNO SAL_TOTAL PRESIDENT_SAL_TOTAL MANAGER_SAL_TOTAL ANALYST_SAL_TOTAL CLERK_SAL_TOTAL SALESMAN_SAL_TOTAL
- ---------- ---------- ------------------- ----------------- ----------------- --------------- ------------------
- 10 8750 5000 2450 1300
- 20 10875 2975 6000 1900
- 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'。
可以指定多个聚合函数,例如统计薪水总合和人数总合:
- tony@ORA11GR2> select * from
- 2 (select deptno, job, sal from emp)
- 3 pivot(
- 4 sum(sal) as SAL_TOTAL, count(sal) as EMP_TOTAL for job in (
- 5 'CLERK' as CLERK,
- 6 'SALESMAN' as SALESMAN
- 7 )
- 8 ) order by 1;
- DEPTNO CLERK_SAL_TOTAL CLERK_EMP_TOTAL SALESMAN_SAL_TOTAL SALESMAN_EMP_TOTAL
- ---------- --------------- --------------- ------------------ ------------------
- 10 1300 1 0
- 20 1900 2 0
- 30 950 1 5600 4
for子句可以指定多列,
为此,先给emp表追加1列rank,取值为'A','B',
- tony@ORA11GR2> alter table emp add (rank varchar2(1) default('A'));
- 表已更改。
- tony@ORA11GR2> update emp set rank=decode(mod(rownum, 2), 0, 'B', rank);
- 已更新14行。
- tony@ORA11GR2> select deptno, job, rank, count(sal) as EMP_TOTAL from emp
- 2 group by deptno, job, rank order by 1, 2;
- DEPTNO JOB RA EMP_TOTAL
- ---------- ------------------ -- ----------
- 10 CLERK B 1
- 10 MANAGER A 1
- 10 PRESIDENT A 1
- 20 ANALYST A 1
- 20 ANALYST B 1
- 20 CLERK A 2
- 20 MANAGER B 1
- 30 CLERK B 1
- 30 MANAGER B 1
- 30 SALESMAN A 2
- 30 SALESMAN B 2
现在,想统计SALESMAN和CLERK的员工中,rank A和rank B各自的人数。
- tony@ORA11GR2> select * from
- 2 (select deptno, job, rank from emp)
- 3 pivot(
- 4 count(rank) as EMP_TOTAL for (job, rank) in (
- 5 ('SALESMAN', 'A') as SALESMAN_A,
- 6 ('SALESMAN', 'B') as SALESMAN_B,
- 7 ('CLERK', 'A') as CLERK_A,
- 8 ('CLERK', 'B') as CLERK_B
- 9 )
- 10 ) order by 1;
- DEPTNO SALESMAN_A_EMP_TOTAL SALESMAN_B_EMP_TOTAL CLERK_A_EMP_TOTAL CLERK_B_EMP_TOTAL
- ---------- -------------------- -------------------- ----------------- -----------------
- 10 0 0 0 1
- 20 0 0 2 0
- 30 2 2 0 1
现在来看看unpivot的用法,
unpivot的语法:
- SELECT ....
- FROM <table-expr>
- UNPIVOT [include nulls|exclude nulls]
- (
- (<column>)
- FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
- ) AS <alias>
- WHERE .....
- DEPTNO CLERK_SAL SALESMAN_SAL
- ------ ---------- ------------
- 10 1300
- 20 1900
- 30 950 5600
用unpivot语句来做列到行的转换,
- tony@ORA11GR2> with t as (
- 2 select * from
- 3 (select deptno, job, sal from emp)
- 4 pivot(
- 5 sum(sal) for job in (
- 6 'CLERK' as CLERK_SAL,
- 7 'SALESMAN' as SALESMAN_SAL
- 8 )
- 9 )
- 10 )
- 11 select * from t
- 12 unpivot(
- 13 SAL_TOTAL for JOB in (
- 14 CLERK_SAL as 'CLERK',
- 15 SALESMAN_SAL as 'SALESMAN'
- 16 )
- 17 ) order by 1,2;
- DEPTNO JOB SAL_TOTAL
- ---------- ---------------- ----------
- 10 CLERK 1300
- 20 CLERK 1900
- 30 CLERK 950
- 30 SALESMAN 5600
如果加上include nulls子句
- tony@ORA11GR2> with t as (
- 2 select * from
- 3 (select deptno, job, sal from emp)
- 4 pivot(
- 5 sum(sal) for job in (
- 6 'CLERK' as CLERK_SAL,
- 7 'SALESMAN' as SALESMAN_SAL
- 8 )
- 9 )
- 10 )
- 11 select * from t
- 12 unpivot include nulls(
- 13 SAL_TOTAL for JOB in (
- 14 CLERK_SAL as 'CLERK',
- 15 SALESMAN_SAL as 'SALESMAN'
- 16 )
- 17 ) order by 1,2;
- DEPTNO JOB SAL_TOTAL
- ---------- ---------------- ----------
- 10 CLERK 1300
- 10 SALESMAN
- 20 CLERK 1900
- 20 SALESMAN
- 30 CLERK 950
- 30 SALESMAN 5600
- DEPTNO CLERK_SAL_TOTAL CLERK_EMP_TOTAL SALESMAN_SAL_TOTAL SALESMAN_EMP_TOTAL
- ------ --------------- --------------- ------------------ ------------------
- 30 950 1 5600 4
- 20 1900 2 0
- 10 1300 1 0
转换为行以后,有2列数据分别显示薪水总合和人数总和。也就是需要对2列同时进行转换。
- tony@ORA11GR2> with t as (
- 2 select * from
- 3 (select deptno, job, sal from emp)
- 4 pivot(
- 5 sum(sal) as SAL_TOTAL, count(sal) as EMP_TOTAL for job in (
- 6 'CLERK' as CLERK,
- 7 'SALESMAN' as SALESMAN
- 8 )
- 9 )
- 10 )
- 11 select * from t
- 12 unpivot include nulls(
- 13 (SAL_TOTAL, EMP_TOTAL) for JOB in (
- 14 (CLERK_SAL_TOTAL, CLERK_EMP_TOTAL) as 'CLERK',
- 15 (SALESMAN_SAL_TOTAL, SALESMAN_EMP_TOTAL) as 'SALESMAN'
- 16 )
- 17 ) order by 1,2;
- DEPTNO JOB SAL_TOTAL EMP_TOTAL
- ---------- ---------------- ---------- ----------
- 10 CLERK 1300 1
- 10 SALESMAN 0
- 20 CLERK 1900 2
- 20 SALESMAN 0
- 30 CLERK 950 1
- 30 SALESMAN 5600 4
关于ANY子句
pivot的一个不便之处是需要在IN子句中指定所有取值,能不能自动完成呢?
比如for job in (select distinct(job) from emp),可惜oracle不支持这么做。
不过oracle支持通过这种方式返回XML格式数据,例如,
- tony@ORA11GR2> select * from
- 2 (select deptno, job, sal from emp)
- 3 pivot XML(
- 4 sum(sal) for job in (ANY)
- 5 ) order by 1;
- DEPTNO JOB_XML
- ---------- --------------------------------------------------------------------------------
- 10 <PivotSet><item><column name = "JOB">CLERK</column><column name = "SUM(SAL)">130
- 0</column></item><item><column name = "JOB">MANAGER</column><column name = "SUM(
- SAL)">2450</column></item><item><column name = "JOB">PRESIDENT</column><column n
- ame = "SUM(SAL)">5000</column></item></PivotSet>
- 20 <PivotSet><item><column name = "JOB">ANALYST</column><column name = "SUM(SAL)">6
- 000</column></item><item><column name = "JOB">CLERK</column><column name = "SUM(
- SAL)">1900</column></item><item><column name = "JOB">MANAGER</column><column nam
- e = "SUM(SAL)">2975</column></item></PivotSet>
- 30 <PivotSet><item><column name = "JOB">CLERK</column><column name = "SUM(SAL)">950
- </column></item><item><column name = "JOB">MANAGER</column><column name = "SUM(S
- AL)">2850</column></item><item><column name = "JOB">SALESMAN</column><column nam
- e = "SUM(SAL)">5600</column></item></PivotSet>
*可以把上面的ANY换成select distinct(job) from emp