OTN Appreciation Day: LISTAGG

von Sabine Heimsath (Kommentare: 1)

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
 

Zurück

Einen Kommentar schreiben

Kommentar von Uwe Küchler |

Voting: Done. :-)
Now I wish for an "Anti-Listagg" to unparse CSV as a built-in...

Cheers,
Uwe

Antwort von Sabine Heimsath

Thanks, Uwe!

Is your request a Database Idea already?