Rows to String

XML - CSV Method

This tutorial shows how to collect values from multiple rows into a single, CSV delimited string. This is also known as "string aggregation". It takes values like these

GROUP_KEY  VAL
---------- ----------
Group 3    a
Group 3    b
Group 3    c
 

and yields a string like this one.

GROUP_KEY  STRING
---------- ---------------
Group 3    a,b,c
 

Oracle's XML features will be used to achieve this result.

In the XML Method tutorial we saw how to use the XMLAGG and XMLELEMENT functions to create an XML delimited string like this.

column string format a62

select
  group_key ,
  XMLAGG
    ( XMLELEMENT( "V", val )
    ).GETSTRINGVAL()
    as string
from
  t
group by
  group_key
;
 
GROUP_KEY  STRING
---------- --------------------------------------------------------------
Group 1    <V>a</V>

Group 2    <V>a</V><V>b</V>

Group 3    <V>a</V><V>b</V><V>c</V>

Group 4    <V>a</V><V>b</V><V>b</V><V>a</V>

Group 5    <V>a</V><V>b</V><V></V><V>e</V><V>d</V>

Group 6    <V></V>

Group 7    <V></V><V></V>

Group 8    <V>a</V><V>b</V><V>b</V><V></V><V></V><V>a</V>

(null)     <V></V>
 

By adding calls to EXTRACT and LTRIM we can convert this solution into one that gives us comma separated values.

select
  group_key ,
  LTRIM
  ( EXTRACT
    ( xmlagg( xmlelement( "V", ',' || val ) ),
      '/V/text()'
    ) ,
    ','
  ) as string
from
  t
group by
  group_key
;
 
GROUP_KEY  STRING
---------- --------------------------------------------------------------
Group 1    a

Group 2    a,b

Group 3    a,b,c

Group 4    a,b,b,a

Group 5    a,b,,e,d

Group 6    (null)

Group 7    (null)

Group 8    a,b,b,,,a

(null)     (null)
 

To control the sort order of values within the string we can use XMLAGG's ORDER BY clause.

select
  group_key ,
  ltrim
  ( extract
    ( xmlagg
      ( xmlelement( "V", ',' || val )
        ORDER BY VAL DESC
      ),
      '/V/text()'
    ) ,
    ','
  ) as string
from
  t
group by
  group_key
;
 
GROUP_KEY  STRING
---------- --------------------------------------------------------------
Group 1    a

Group 2    b,a

Group 3    c,b,a

Group 4    b,b,a,a

Group 5    e,d,b,a

Group 6    (null)

Group 7    (null)

Group 8    b,b,a,a

(null)     (null)
 

As with the original solution, you can use a FROM clause subquery to produce distinct values and filter out null values if you need to.

select
  group_key ,
  ltrim
  ( extract
    ( xmlagg
      ( xmlelement( "V", ',' || val )
        order by val
      ),
      '/V/text()'
    ) ,
    ','
  ) as string
from
  ( SELECT DISTINCT GROUP_KEY, VAL -- produces distinct values
    FROM   T
    WHERE  VAL IS NOT NULL         -- filters our null values
  ) tb
group by
  group_key
;
 
GROUP_KEY  STRING
---------- --------------------------------------------------------------
Group 1    a

Group 2    a,b

Group 3    a,b,c

Group 4    a,b

Group 5    a,b,d,e

Group 8    a,b
 

Acknowledgements

These solutions were adapted from one by OTN user "micheals" at OTN SQL and PL/SQL Forum - Re: Return multi-row result as one comma-delimited row.

See Also

An alternate solution by OTN user Aketi Jyuuzou is available at OTN SQL and PL/SQL Forum - Re: how to display empno in a single row instead of displaying it vertically




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-11785.html]SQL Snippets: Rows to String - XML - CSV Method[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-11785.html">SQL Snippets: Rows to String - XML - CSV Method</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Rows to String - XML - CSV Method
  • URL (href): http://www.sqlsnippets.com/en/topic-11785.html

Revision Notes

Date Category Note
2009-07-24 Revision Added sorting solution using the XMLAGG ORDER BY clause as suggested by Austin Beer.
2007-05-10 Revision Replaced REGEXP_REPLACE logic with calls to EXTRACT and LTRIM.