Oracle列转行函数listagg和vmsys.vm_concat

[原]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,[字符])可以指定拼接字符（可以指定为‘,’、‘/’等）。

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

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 多行记录合并/连接/聚合字符串的几种方法