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 🙂