[原]Oracle列转行函数listagg和vm_concat
在使用oracle查询时经常会遇到将某列的多个值转化为一行显示的情况,例如如下情况:
employinfo:
-----------------------------------------------------
deptno nameage
-----------------------------------------------------
10 jerry 24
20 tom 26
30 lili 25
10 blue 21
30 prince 30
10 angela 28
-----------------------------------------------------
现需要以如下方式汇总部门人员信息
---------------------------------------------------------------
deptno employeeinfo
---------------------------------------------------------------
10 jerry(24),blue(21),angela(28)
20 tom(26)
30 lili(25),prince(30)
---------------------------------------------------------------
oracle提供了两个函数可以很方便的实现列转行信息汇总,这两个函数分别是vm_concat和listagg,接下来我们一起看看其使用方法。
使用vm_concat函数实现:
SELECT deptno, wm_concat (name || '(' || age || ')') employeeinfo
FROM employinfo
GROUP BY deptno
ORDER BY deptno;
显示结果:
DEPTNO | EMPLOYEEINFO |
10 | jerry(24),angela(28),blue(21) |
20 | tom(26) |
30 | lili(25),prince(30) |
使用listagg函数实现:
SELECT deptno,
listagg (name || '(' || age || ')', ',')
WITHIN GROUP (ORDER BY deptno)
employeeinfo
FROM employinfo
GROUP BY deptno;
显示结果:
DEPTNO | EMPLOYEEINFO |
10 | angela(28)/blue(21)/jerry(24) |
20 | tom(26) |
30 | lili(25)/prince(30) |
两个函数函数都可以将列转化为行显示,但是在连接列值时有所不同,vm_concat(column)函数只有一个参数,就是需要拼接的列,其默认拼接字符串为‘,’。而listagg(column,[字符])可以指定拼接字符(可以指定为‘,’、‘/’等)。
![](https://i-blog.csdnimg.cn/blog_migrate/e926bdd1d2cdbf7e43bc7cb3f0c9954b.gif)
--vmsys.vm_concat函数使用 如下所示,按部门进行分组,同一组的在一行中用逗号隔开
SELECT deptno, wmsys.wm_concat(ename) FROM emp GROUP BY deptno;
listagg函数:
SELECT sys_connect_by_path(ename, ',')
FROM (SELECT ename, deptno, rownum rn FROM emp)
START WITH rn = 1
CONNECT BY rn = rownum;
--substr从第二个开始截取,去掉第一个逗号
SELECT substr(sys_connect_by_path(ename, ','), 2)
FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
START WITH rn = 1
CONNECT BY rn = rownum;
--值截取最大的最后一条记录(与vmsys.vm_concat函数有点不同,不能按某个字段进行group by,而是不断的累积)
SELECT max(substr(sys_connect_by_path(ename, ','), 2))
FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
START WITH rn = 1
CONNECT BY rn = rownum;
--实现与上述max一样的效果(connect_by_isleaf只取出为叶节点的记录)
SELECT substr(sys_connect_by_path(ename, ','), 2),connect_by_isleaf
FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
WHERE connect_by_isleaf = 1
START WITH rn=1
CONNECT BY prior rn = rn-1;