Oracle列转行函数listagg和vmsys.vm_concat

来源：互联网发布：关于c语言的书那本最好编辑：程序博客网时间：2024/06/05 00:46

[原]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;

显示结果：

DEPTNOEMPLOYEEINFO10jerry(24),angela(28),blue(21)20tom(26)30lili(25),prince(30)

使用listagg函数实现：

SELECT deptno,
listagg (name || '(' || age || ')', ',')
WITHIN GROUP (ORDER BY deptno)
employeeinfo
FROM employinfo
GROUP BY deptno;

显示结果：

DEPTNOEMPLOYEEINFO10angela(28)/blue(21)/jerry(24)20tom(26)30lili(25)/prince(30)

两个函数函数都可以将列转化为行显示，但是在连接列值时有所不同，vm_concat(column)函数只有一个参数，就是需要拼接的列，其默认拼接字符串为‘,’。而listagg(column,[字符])可以指定拼接字符（可以指定为‘,’、‘/’等）。

=============================================================

listagg，vmsys.vm_concat与sys_connect_by_path函数

listagg与vmsys.vm_concat：可以实现行转成列，并以逗号分开的效果。

区别：listagg是11.2新增的函数，且该函数可以实现组内的排序

--vmsys.vm_concat函数使用如下所示，按部门进行分组，同一组的在一行中用逗号隔开
SELECT deptno, wmsys.wm_concat(ename) FROM emp GROUP BY deptno;

listagg函数：

sys_connect_by_path函数：

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;

« 上一篇：在windows2003系统上安装两个版本的oracle
» 下一篇：v$与v_$视图关系

参考：

Oracle 多行记录合并/连接/聚合字符串的几种方法

0 0