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

Comments

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

    ReplyDelete

Post a Comment

Popular posts from this blog

Sitecore Social Connected 1.3.0 Add Account issue (fixed)