Aggregate multiple rows into single row using String Aggregation in Oracle 11g

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.

DEPT	NAME
IT	Ateeq
IT	Ashraf
IT	Nizam
IT	Rizwan
SCMS	Azhar
SCMS	Javed
Marketing	Asem
Marketing	Joo

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