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: select rtrim(dbms_xmlgen.convert(xmlagg(xmlelement(e, 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": http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/ora19011-character-string-buffer-too-small-when-used-with-xmlagg-function-3882635 The dbms_xmlgen.convert trick came from "HOWTO: Avoid Conversion of Escape Codes": http://www.liberidu.com/blog/?p=635 Enjoy :-)
