Oracle的行转列和列转行

Oracle行列互转函数的学习：

  1、WM_CONCAT

  用scott用户自带的emp表作为例子，查询每个部门下所有的雇员的名字：

select deptno,wm_concat(ename) from emp group by deptno;

   查询结果为:

  2、LISTAGG

  oracle新增的listagg函数也是对字符串的行列转换，但是功能比wm_concat功能更加强大，它可以和开窗函数over()一起使用

  listagg的语法：LISTAGG( [字段名,分隔符]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

  同样以scott用户自带的emp表做为例子，还是查询每个部门下所有雇员的名字，注意group里面的order by 不能为空：

 按雇员的姓名排序
select deptno,listagg(ename,',') within group (order by ename) from emp group by deptno;

  listagg函数还可以和over()开窗函数一起使用:

select deptno,listagg(ename,',') within group (order by ename) over(partition by deptno) from emp;

  3、PIVOT和UNPIVOT

   PIVOT:

    oracle中我们可以使用case when end 或者 decode 函数做到行列互转的功能，这有的优点是使用范围广，但是编写和维护麻烦。而oracle11g中的pivot函数可以很简单的实现行列互转，但是使用范围有限制。

  还是使用scott用户下的emp用来做数据源，现在需要统计每个工作在每个部门的工资总和，一个部门用一列表示：

  先使用case when end来实现：

select job,
SUM(case when DEPTNO = 10 THEN SAL END) as 部门10工资,
SUM(case when DEPTNO = 20 THEN SAL END) as 部门20工资,
SUM(case when DEPTNO = 30 THEN SAL END) as 部门30工资
from emp group by job

  使用pivot函数实现：

select * from (select job,sal,deptno from emp) pivot(sum(sal) for deptno in (10 as d10,20 as d20,30 as d30))

  可以发现pivot函数简单更多，而且更容易扩展，假设现在还要把奖金也统计进去，那么用case when end 就需要添加三行，而pivot只需添加一个字段

select * from (select job,sal,deptno,comm from emp) pivot(sum(sal) as s,sum(comm) as c for deptno in (10 as d10,20 as d20,30 as d30))

  pivot函数的语法：pivot(聚合函数 as 总的别名... for 字段 in (字段A as 别名1 ，字段B as 别名2...)

 通过查看sql语句的PLAN，我们发现实际上pivot实际上也是使用的case when end，只是写法更简单了。