Listparts, vmsys. vm_concat and sys_connect_by_path Functions

Listparts and vmsys. vm_concat: Convert rows into columns and separate them with commas. Difference: listparts is a new function added in 11.2, and this function can be used to sort in groups.

-- The vmsys. vm_concat function is grouped by department as follows. The same group is separated by commas (,) in a row.
Select deptno, wmsys. wm_concat (ename) from EMP group by deptno;

Listparts function:

Sys_connect_by_path function:

Select sys_connect_by_path (ename ,',')
From (select ename, deptno, rownum rn from EMP)
Start with Rn = 1
Connect by Rn = rownum;

 

-- Substr is truncated from the second and the first comma is removed.
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;

 

-- Truncate the maximum last record (unlike the vmsys. vm_concat function, which does not support group by based on a field, but accumulates continuously)
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;

 

-- Achieve the same effect as the above max (Connect_by_isleaf only records of leaf nodes are retrieved.)
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;