0

I'm looking for an aggregate function supported by Oracle SQL to collect values into arrays, something similar to PostgreSQL ARRAY_AGG. I already use listagg for strings, I use cast() with collect() and multiset() but it requires to declare a table type. Is there a more straightforward way of emulating ARRAY_AGG with Oracle SQL?

1 Answer 1

0

It's possible to emulate ARRAY_AGG with Oracle SQL without declaring new types only in very simple cases when build-in types can do the job, for example sys.odcivarchar2list (varray of varchar2) and sys.odcinumberlist (varray of number), with cast() and collect() or multiset():

select 
   cast(multiset(select id from my_pretty_table order by id) as sys.odcinumberlist)
from 
   dual;

select
   cast(collect(id order by id) as sys.odcinumberlist)
from
   my_pretty_table;

Note that this solution is still a lot less flexible than PostgreSQL ARRAY_AGG.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.