Rows to String

The tutorials in this section show how to collect values from multiple rows into a single, delimited string. This process is commonly known as "string aggregation". In practice it takes a table that looks like this

GROUP_KEY  VAL
---------- ----------
Group 1    a

Group 2    a
Group 2    b

Group 3    a
Group 3    b
Group 3    c

Group 4    a
Group 4    a
Group 4    b
Group 4    b

Group 5    a
Group 5    b
Group 5    d
Group 5    e
Group 5    (null)

Group 6    (null)

Group 7    (null)
Group 7    (null)

Group 8    a
Group 8    a
Group 8    b
Group 8    b
Group 8    (null)
Group 8    (null)

(null)     (null)
 

and yields a set of strings that look like this.

GROUP_KEY  STRING
---------- ---------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,a,b,b
Group 5    a,b,d,e,
Group 6    (null)
Group 7    ,
Group 8    a,a,b,b,
(null)     (null)
 

Over the years many techniques for performing string aggregation have appeared. They all have their strengths and weaknesses so a few different methods will be examined here. Of the methods I've found on the web to-date we will explore four popular approaches.

As well, in the tutorials

I present some additional techniques I created.

The feature graph below maps string aggregation techniques to features. Knowing which features are available with each technique will help you choose the best technique for your requirements.

Feature MAX DECODE LEAD MODEL Hierarchical Object Object + MODEL STRAGG XML CSV
Pure SQL solution, no custom objects required Y Y Y Y       Y
Can include null values
  e.g. (a,b,null,d,e,null) -> 'a,b,d,e,,'
Y Y Y Y Y
Can exclude null values
  e.g. (a,b,null,d,e,null) -> 'a,b,d,e'
Y Y Y Y Y Y Y Y
Can produce either distinct or duplicate values
  e.g. (a,a,b,b) -> 'a,a,b,b' or 'a,b'
Y Y Y Y Y Y Y Y
Can control sort order of values within the string
  e.g. (a,b,c) -> 'a,b,c' or 'c,b,a'
Y Y Y Y Y Y Y
Works in versions prior to 10g Y Y Y Y Y
Can aggregate an indefinite number of values Y Y Y Y Y Y

While the Hierarchical approach has the most features, it is also the slowest of all these methods. See Performance Comparison Charts for more details.

In the tutorials to follow all examples will produce comma separated value (CSV) strings. However, the techniques can easily be adapted to produce pipe delimited strings, tab delimited strings, or strings delimited with any given character.

In addition to these techniques the tutorial

XML Method

demonstrates how to produce a string of values formatted as an XML fragment.

Tags

"concatenating rows" "merging more than one record into one"
"combining multiple rows into one" "retrieving rows into columns"
"viewing data laterally" "displaying row data horizontally"
"aggregating string data"



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-11787.html]SQL Snippets: SQL Techniques Tutorials - Rows to String[/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-11787.html">SQL Snippets: SQL Techniques Tutorials - Rows to String</a>

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

  • Link Text : SQL Snippets: SQL Techniques Tutorials - Rows to String
  • URL (href): http://www.sqlsnippets.com/en/topic-11787.html

Revision Notes

Date Category Note
2007-04-12 Updated Tutorial

The SQL Snippets "Rows to String" tutorial section (which contains tutorials originally published in the "Delimited Strings" section) has been updated with the following changes.

  • two new string aggregation techniques, MODEL Method 1 and MODEL Method 2, have been added
  • the original MODEL method (now called MODEL Method 3) has been simplified and now uses less memory than before
  • the performance comparison tests show results for a 100,000 row table now instead of a 10,000 row table

(Apologies to those of you who experienced navigation difficulties in this section over the last few days. The menu item links have been fixed and you should have no further problems.)