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;