Concatenate rows in Oracle

Recently, I had to concatenate and put in one line (comma separated) the results of a query in Oracle. After one version using replace, xmlagg and xmlelement which had several problems and some reading online I compiled the following code:

mycolumn || ', ')).extract('//text()').getClobVal(), 1),
', ')

from mytable

The extract/getClobVal trick came from "ORA-19011 Character string buffer too small when used with XMLAGG function":

The dbms_xmlgen.convert trick came from "HOWTO: Avoid Conversion of Escape Codes":

Enjoy :-)


  1. Hi Alek,
    This workd great. But, I see duplicates in the coma separated values column, How do I remove them?


Post a Comment

Popular posts from this blog

Крачка назад във вечния танц