Aug 11, 2010

LISTAGG alternative in 10g

LISTAGG aggregate function was introduced in 11g. It allows to aggregate into a string some values with delimiters. But what if you are still didn't upgrade your DB? There are several options:

WM_CONCAT is acceptable alternative some times. It doesn't allow you to specify a delimiter or sort order:

WITH Q AS
(
    SELECT 1 X FROM DUAL  UNION ALL
    SELECT 2   FROM DUAL  UNION ALL
    SELECT 3   FROM DUAL  UNION ALL
    SELECT 9   FROM DUAL   
)
SELECT WM_CONCAT(Q.X)
FROM Q

More options - http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

More interesting example using XMLAgg function:

WITH Q AS
(
    SELECT 1 key, 'Zend' X  FROM DUAL  UNION ALL
    SELECT 1, '2'           FROM DUAL  UNION ALL
    SELECT 1, 'Cage'        FROM DUAL  UNION ALL
    SELECT 1, '9'           FROM DUAL  UNION ALL   
    SELECT 2, 'take' X      FROM DUAL  UNION ALL
    SELECT 2, '77'          FROM DUAL  UNION ALL
    SELECT 2, '15'          FROM DUAL  UNION ALL
    SELECT 2, 'lime'        FROM DUAL
)
SELECT   KEY,
         RTRIM(
            XMLAGG (XMLELEMENT(e, x||'; ') ORDER BY x).EXTRACT('//text()')
         ) AS concatval
    FROM q
GROUP BY KEY
This one is much more powerful and flexible - you can specify both delimiters and sort order within each group.

It is also HTML-safe, eg. letter '>' will be converted to > tag. Though it could be also a cons, if you are using this for not a web application.

6 comments:

Quinen said...

XMLAGG is a great solution, thanx !

rusty said...

THX!
This was very useful for me!

Anonymous said...

Brilliant, worked a treat as not using 11gR2. Note that in your example the RTRIM function needs amending to RTRIM(string,'; ') to remove the trailing semi-colon.

Paul Brookfield said...

Just lovely, many thanks for the pointer. Saved my backside fixing a fault I introduced into 10g by writing with 11g in mind...

Anonymous said...

Great thing! Thank you very much) Searching for two days to combine few rows to one without any ids and WM_CONCAT.

Ahmed Saleh said...

Very very interesting XMLAGG solution