listagg

Oracle – ORA-01489: result of string concatenation is too long [duplicate]

by

You are exceeding the SQL limit of 4000 bytes which applies to LISTAGG as well. SQL> SELECT listagg(text, ‘,’) WITHIN GROUP ( 2 ORDER BY NULL) 3 FROM 4 (SELECT to_char(to_date(level,’j’), ‘jsp’) text FROM dual CONNECT BY LEVEL < 250 5 ) 6 / SELECT listagg(text, ‘,’) WITHIN GROUP ( * ERROR at line 1: … Read more

LISTAGG in Oracle to return distinct values

by

19c and later: select listagg(distinct the_column, ‘,’) within group (order by the_column) from the_table 18c and earlier: select listagg(the_column, ‘,’) within group (order by the_column) from ( select distinct the_column from the_table ) t If you need more columns, something like this might be what you are looking for: select col1, listagg(col2, ‘,’) within group … Read more