Oracle SQL: LISTAGG

listagg is an aggregate function that allows to concatenate values of different records that belong to the same group into one record.

19c improves listagg with the DISTINCT clause.

Example

create table tq84_listagg (
  id    number,
  txt   varchar2(10)
);

insert into tq84_listagg values (1, 'one' );
insert into tq84_listagg values (1, 'eins');
insert into tq84_listagg values (1, 'un'  );

insert into tq84_listagg values (2, 'two' );
insert into tq84_listagg values (2, 'duo' );

insert into tq84_listagg values (3, 'three');
insert into tq84_listagg values (3, 'tres' );
insert into tq84_listagg values (3, 'trois');
insert into tq84_listagg values (3, 'drei' );

column txt_  format a40

select
  id,
  listagg(txt, '-') within group (order by txt) as txt_
from
  tq84_listagg
group by
  id;

drop table tq84_listagg purge;

Github repository Oracle-Patterns, path: /SQL/select/group_by/listagg.sql

The result of the query is

ID TXT_
-- ----------------------
1  eins-one-un
2  duo-two
3  drei-three-tres-trois

ORA-01489: result of string concatenation is too long

listagg can only return up to 4000 characters so that the following statement results in an ORA-01489: result of string concatenation is too long error:

select
   listagg(level, '-')
from
   dual connect by level < 1023;

Such errors can be omitted with the listagg overflow clause:

select
   listagg(level, '-' on overflow truncate '...' without count)
from
   dual connect by level < 1023;

With the Data Cartridge Interfaces, it is possible to write a user defined aggregate function that offers a similar functionality like listagg, but returns a CLOB.

An example of such a user defined function is here.

Oracle SQL: LISTAGG

Example

ORA-01489: result of string concatenation is too long

See also