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