String Aggregation in Oracle

String Aggregation

It is sometime important to aggregate data from number of rows into a single row. Oracle 11g release 2 has provided us LISTAGG function to achieve the same.

Below is an example:

Let’s say we have the following data.



And we want to group the data into single row using DEPT:

SELECT DEPT, LISTAGG(NAME, ’ ’) WITHIN GROUP (ORDER BY NAME) AS STAFF FROM STAFF

Output:

IT: Ateeq,Ashraf,Nizam,Rizwan
SCMS: Azhar,Javed
Marketing: Asem,Joo