Loading

String Aggregation in Oracle

Ashraf Ansari

1 Comment

Join the conversation! Your thoughts help the community grow.

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

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.

table

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:

SCMS:

Marketing: