Opposite to ListAgg Function

Example: (single Record)

 Single record 'Trinesh,Shanthi,Tejesh,Vasavi' need to convert into 4 records as follows

Trinesh

Shanthi

Tejesh

Vasavi 

using simple SQL query.

Query:

SELECT REGEXP_SUBSTR('Trinesh,Shanthi,Tejesh,Vasavi','[^,]+',1,LEVEL) NAMES FROM DUAL

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('Trinesh,Shanthi,Tejesh,Vasavi','[^,]+'))+1

Example: (Multiple Record)

Source:

 Query:

ListAgg Example

Example:

How to display department_id and corresponding employees name delimited with ',' operator?

For Example:

Data looks as follows:

We need to convert above data as follow:

Query:

SELECT DEPARTMENT_ID , 

LISTAGG(FIRST_NAME||'.'||LAST_NAME,', ') WITHIN GROUP (ORDER BY FIRST_NAME) EMPLOYEE_LIST FROM HR.EMPLOYEES

GROUP BY DEPARTMENT_ID;