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
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" |