Why do I like LISTAGG?
I use it quite often for reporting purposes or when I’m building scripts from metadata, for instance all_tab_cols:
select 'create unique index ' || t1.table_name || '_IDX on '
|| t1.table_name || ' ( '
|| listagg (t1.column_name, ', ')
within group (order by column_id)
|| ' );' idx_stmt
from user_tab_cols t1
where column_name like '%ID'
group by t1.table_name;which returns statements like this:
create unique index DEPARTMENTS_IDX on DEPARTMENTS ( DEPARTMENT_ID, MANAGER_ID, LOCATION_ID );
What I don’t like about LISTAGG…
LISTAGG has a limit of 4000 characters.
The only workaround I know is Carsten Czarski’s LISTAGG_CLOB function (German/English).
If you are on 12c, there is another alternative: Instead of the function throwing an error you can have the result truncated by defining the listagg_overflow_clause.
The future?
Something that I still miss is the possibility to get rid of duplicates within the results.
Of course I could use DISTINCT in a subquery before I apply LISTAGG, but this only works for one column (if I want to keep it simple).
If you see a DISTINCT option as an improvement for the LISTAGG function, please support the idea on OTN Database Ideas:
The only thing you need is a (free) OTN account, which you have anyway, don’t you?
OTN Appreciation Day – what is it?
The idea of the OTN Appreciation Day was initiated by Tim Hall, read more about it here: OTN Appreciation Day
