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,只是写法更简单了。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值