This is one of the numerous functions that I was not aware of until today. LISTAGG orders data within each group and concatenates the values of the measure column. The keyword here is concatenates.
For example, you are required to display all the employees within a department in one comma separated row item.
SELECT DISTINCT NVL(department_id, '0') Department_ID, listagg(first_name, ',') within GROUP ( ORDER BY first_name) over (partition BY department_id) FROM employees ORDER BY 1;
The ORDER BY first_name is to order the data within each group specified by the OVER clause. The data is partitioned by department_id and then sorted on first_name. The output to this query is as follows –
The same query can also be written without the OVER clause by simple using GROUP BY –
SELECT NVL(department_id, 0) Department_ID, listagg(first_name, ',') within GROUP ( ORDER BY first_name) Employees FROM employees GROUP BY department_id ORDER BY 1;