Tuesday, December 7, 2010

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 :-)

2 comments: