String concatenation aggregation methods

This article describes method to aggregate and concatenate strings.

CREATE TABLE test_tbl
(
 grp_id   NUMBER,
 elm_id   NUMBER,
 elm_name VARCHAR2(25)
);

INSERT INTO test_tbl
SELECT 1, LEVEL, 'my name 1_'||LEVEL 
FROM dual 
CONNECT BY level < 5;

INSERT INTO test_tbl
SELECT 2, LEVEL, 'my name 2_'||LEVEL
FROM dual
CONNECT BY LEVEL < 5;

INSERT INTO test_tbl
SELECT 3, LEVEL, 'my name 3_'||LEVEL
FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

We have following data

SELECT * FROM test_tbl
ORDER BY 1, 2;

    GRP_ID     ELM_ID ELM_NAME                
---------- ---------- -------------------------
         1          1 my name 1_1              
         1          2 my name 1_2              
         1          3 my name 1_3              
         1          4 my name 1_4              
         2          1 my name 2_1              
         2          2 my name 2_2              
         2          3 my name 2_3              
         2          4 my name 2_4
         3          1 my name 3_1              
...
         3       1000 my name 3_1000

and purpose is to covert it to following layout

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4
      3  my name 3_1,...                    ,my name 3_1000

LISTAGG

First method is using standard LISTAGG function. This function is nice but has own limits CONVERTED string can’t be longer than 4000 bytes so quite often happens it’s useless in professional projects.

SELECT 
  grp_id, 
  LISTAGG(elm_name, ',') WITHIN GROUP(ORDER BY elm_id) CONVERTED
FROM test_tbl
WHERE grp_id in (1,2)
GROUP BY grp_id;

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4

SELECT 
  grp_id, 
  LISTAGG(elm_name, ',') WITHIN GROUP(ORDER BY elm_id) CONVERTED
FROM test_tbl
WHERE grp_id in (3)
GROUP BY grp_id;

ORA-01489: result of string concatenation is too long

XML method

Other method is to use pure XML. It works nice the only disadvantage I find it here is very long statement which is hard to read

SELECT 
  grp_id, 
  RTRIM(XMLAGG(XMLELEMENT(E,elm_name,',').EXTRACT('//text()') 
        ORDER BY elm_id).GETCLOBVAL(),',') AS CONVERTED
FROM TEST_TBL
GROUP BY grp_id;

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4
      3  my name 3_1,my name 3_2,...,my name 3_999,my name 3_1000

COLLECTION

Another option that can be used is creation of own function that is based on own type. It’s easier to read than XML but requires creation of types and function

CREATE OR REPLACE TYPE t_agg_rec AS OBJECT
(
  str_rec CLOB
)
/

CREATE OR REPLACE TYPE t_agg_tbl FORCE AS TABLE OF t_agg_rec;
/

CREATE OR REPLACE FUNCTION fn_converted
(
  l_agg_tbl   IN t_agg_tbl, 
  l_delim     IN VARCHAR2
)
RETURN CLOB 
IS
  l_str CLOB;
BEGIN
  IF l_agg_tbl.COUNT > 0 THEN
    FOR i IN l_agg_tbl.FIRST .. l_agg_tbl.LAST 
    LOOP
      IF i != l_agg_tbl.FIRST THEN
        l_str := l_str || l_delim;
      END IF;
      
      l_str := l_str || l_agg_tbl(i).str_rec;
    END loop;
  END IF;
  
  RETURN l_str;
END fn_agg_del;
/

SELECT
  grp_id, 
  fn_converted
  ( cast(collect(t_agg_rec(elm_name) ORDER BY elm_id) as t_agg_tbl), 
    ',' ) CONVERTED 
FROM test_tbl
GROUP BY grp_id

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4
      3  my name 3_1,my name 3_2,...,my name 3_999,my name 3_1000

Own aggregate function

You can write your own aggregate function that is described in following article: Oracle user defined aggregate function but it’s not possible to pass here delimiter and order elements by extra column

Have a fun 🙂

Tomasz

Leave a Reply

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