Equivalent of PostgreSQL's array_agg in Oracle XE 11.2

Question

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?

tl;dr - `LISTAGG()` was added in 11g and will give you the result set you need. — Ben, Dec 17 '17 at 15:36

Kaushik Nayak · Accepted Answer · 2017-12-18T02:32:50.013

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

i have added below two record to the table: INTO yourtable (ID, Category) VALUES (2, 'Cat4') INTO yourtable (ID, Category) VALUES (2, 'Cat4') when i use listagg query i got 2 Cat3,Cat4,Cat4i,Cat4 result. How can i avode duplicate values in listagg? — Murmelto, Dec 17 '17 at 19:40

score 2 · Answer 2 · answered Dec 17 '17 at 15:41

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.

Equivalent of PostgreSQL's array_agg in Oracle XE 11.2

2 Answers2

Linked

Related