LISTAGG Enhancements

If you consider to aggregate data from multiple rows into a single column, LISTAGG might be the first function that comes to your mind. It has been enhanced from Oracle 12c onwards. Let’s create a sample data set to illustrate the main features of LISTAGG function.

All examples has been performed in Oracle 19c.

CREATE TABLE T_TEST
AS
SELECT LEVEL ID, DBMS_RANDOM.STRING('x',10) COL1 FROM DUAL CONNECT BY LEVEL <= 1000;

SELECT 
    LISTAGG(COL1, ', ') WITHIN GROUP(ORDER BY ID) 
FROM T_TEST;

3 sat²r²nda HATA:
ORA-01489: dize biti■tirmenin sonucu ok uzun

If the length of the concatenated data is more than 4000 characters, you will get the ORA-01489 result of string concatenation is too long error. In order to avoid this error, we can add ON OVERFLOW TRUNCATE clause.

SELECT 
    LISTAGG(COL1, ', ' ON OVERFLOW TRUNCATE) WITHIN GROUP(ORDER BY ID) AGG_COL1
FROM T_TEST;


AGG_COL1
--------------------------------------------------------------------------------
T1BF4QK2YZ, XG4LO0DZD1, 5ULL5SF2LV, 0GDMI0HTLI, LYMTX4DGQ1, 7WGZMZTQHQ, MKICMGHM
.
.
.
E1, D9ALE8Q76F, VQ8UKQSQ8P, 3IZ7QD54FU, ...(670)

By default ellipsis is added at the end of the truncated string. We can change the default value as shown below.

SELECT 
    LISTAGG(COL1, ', ' ON OVERFLOW TRUNCATE 'String is too long') 
    WITHIN GROUP(ORDER BY ID) AGG_COL1
FROM T_TEST;


AGG_COL1
--------------------------------------------------------------------------------
T1BF4QK2YZ, XG4LO0DZD1, 5ULL5SF2LV, 0GDMI0HTLI, LYMTX4DGQ1, 7WGZMZTQHQ, MKICMGHM
.
.
.
E1, D9ALE8Q76F, VQ8UKQSQ8P, String is too long(671)

We can also exclude the count by adding WITHOUT COUNT.

SELECT 
    LISTAGG(COL1, ', ' ON OVERFLOW TRUNCATE 'String is too long' WITHOUT COUNT) 
    WITHIN GROUP(ORDER BY ID) AGG_COL1
FROM T_TEST;


AGG_COL1
--------------------------------------------------------------------------------
T1BF4QK2YZ, XG4LO0DZD1, 5ULL5SF2LV, 0GDMI0HTLI, LYMTX4DGQ1, 7WGZMZTQHQ, MKICMGHM
.
.
.
E1, D9ALE8Q76F, VQ8UKQSQ8P, 3IZ7QD54FU, TCXAETB8SD, String is too long

Finally, in order the get the whole data without splitting it, XMLAGG function can be used as below.

SELECT
    RTRIM(XMLAGG(XMLELEMENT(e, col1||',') ORDER BY ID).EXTRACT('//text()').GetClobVal(), ',') result
FROM T_TEST;

I hope this helps 🙂

Posted in SQL
Leave a Reply

Your email address will not be published. Required fields are marked *