Equivalent of PostgreSQL's array_agg in Oracle

Question

This question already has answers here:

SQL Query to concatenate column values from multiple rows in Oracle (10 answers)

Closed 2 years ago.

I have a Oracle 11g XE database and I have a query the result set:

ID   Category
1    Cat1
1    Cat2
2    Cat3
2    Cat4

I want to get distinct id's with all related categories in same row as comma separated like this

ID   Categories
1    Cat1,Cat2
2    Cat3,Cat4

I was using Postgres before and array_agg helped me there. How can I get same result in Oracle 11g XE?

Answer 1

Unless you are using it in a stored procedure to save the output as an array(or collection), a query with LISTAGG should be sufficient and gives the same output.

select ID , LISTAGG(Category,',') WITHIN GROUP ( ORDER BY ID ) Categories
FROM yourtable GROUP BY ID;

In oracle, we do not have have a straightforward conversion function like array_agg. However, you can create a user-defined collection type, then use CAST and COLLECT functions to convert it to a NESTED TABLE to get the same desired output.

Firstly, create a collection TYPE.

create or replace TYPE categorytype as TABLE OF VARCHAR2(20);

Now, running this query is equivalent to using string_agg or LISTAGG, although categories is an array or collection, rather than a string.

select id, CAST ( COLLECT(Category) as categorytype ) categories
FROM yourtable group by id;

| ID | CATEGORIES |
|----|------------|
|  1 |  Cat1,Cat2 |
|  2 |  Cat3,Cat4 |

DEMO

Answer 2

Oracle has a function listagg() which is pretty close to Postgres's string_agg(). Both seem to do what you want to do.

However, array_agg() creates an array using a SQL expression. I don't think Oracle supports arrays as native types in SQL statements (although arrays are part of PL/SQL). Hence there is no equivalent.