String Aggregation in Oracle SQL using XMLAGG

Requirement

We have table test5 with following data
c1
-----
ADD
DELETE
MODIFY

Required output
col
------
ADD,DELETE.MODIFY

i.e we want column value to be aggregated with comma.

Solution
In real scenario we will have another column on which we need to group and then aggregate corresponding values.

In oracle 11g there is one function wm_concat which concatenates column values with comma. We can write a procedure to perform this thing but here we will  try  to do it with sql query alone.

This solution uses XMLELEMENT, XML_AGGR functions

[sourcecode language="sql"]
SELECT replace (replace (replace (XMLELEMENT ("col" , XMLAGG(XMLELEMENT("col", c1))), '</col><col>' , ',' ),'<col><col>' , '' ),'</col></col>', '')
as "col"  FROM test5
[/sourcecode]

Output
col
------
ADD,DELETE,MODIFY