SQL Snippets ™: Tutorials for Oracle Developers

This tutorial shows how to collect values from multiple rows into a single, comma 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.

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

Oracle's object relational features will be used to achieve this result. Other tutorials in this section present alternate approaches.

Prerequisites

This method uses the object relational SQL COLLECT function which was introduced in Oracle 10g. It therefore will not work in earlier versions. It also requires a VARCHAR2 table type to work. The VARCHAR2 table type is created in the Setup topic for this section.

desc varchar2_table_type
 varchar2_table_type TABLE OF VARCHAR2(10)

 

Unlike the solution at Object Method, this solution does not require a custom function to convert a nested table to a CSV string.

Test Data

desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 GROUP_KEY                              VARCHAR2(10)
 VAL                                    VARCHAR2(10)

set null "(null)"

break on group_key skip 1 duplicates

select * from t order by group_key, val ;
 

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)
 

The Solution

We can now run the solution, which uses the TYPE created above in conjunction with the COLLECT function and the MODEL clause.

clear breaks

select
  group_key ,
  ( select substr(string,2)
    from   table( ta.string_table )
    model
      return updated rows
      dimension by ( row_number() over (order by column_value) as key )
      measures     ( cast( column_value as varchar2(65) ) as string )
      rules
        upsert
        iterate( 6 )
          until ( presentv(string[iteration_number+2],1,0) = 0 )
        ( string[0] = string[0] || ',' || string[iteration_number+1] )
  ) string
from
  ( select group_key, cast( collect(val) as varchar2_table_type ) as string_table
    from t
    group by group_key
  ) ta
;
 

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    (null)
Group 8    a,a,b,b
(null)     (null)
 

There are two important things to note about this solution. First, like the Object Method solution, these results do not include empty values in the strings for Groups 5 and 8. As well, the string for "Group 7" only contains one null, not two. This is because COLLECT is an aggregate function and all aggregate functions, except COUNT(*) and GROUPING, ignore null values.

Second, unlike the Object Method solution, we can control the sort order of values in the string with the ROW_NUMBER() function's ORDER BY argument. For example, this version sorts the string values in descending order.

select
  group_key ,
  ( select substr(string,2)
    from   table( ta.string_table )
    model
      return updated rows
      dimension by ( row_number() over (ORDER BY COLUMN_VALUE DESC) as key )
      measures     ( cast( column_value as varchar2(65) ) as string )
      rules
        upsert
        iterate( 6 )
          until ( presentv(string[iteration_number+2],1,0) = 0 )
        ( string[0] = string[0] || ',' || string[iteration_number+1] )
  ) string
from
  ( select group_key, cast( collect(val) as varchar2_table_type ) as string_table
    from t
    group by group_key
  ) ta
;
 

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)
 

To filter out duplicate values, like those in Groups 4 and 8, we simply add the DISTINCT argument to the COLLECT function.

select
  group_key ,
  ( select substr(string,2)
    from   table( ta.string_table )
    model
      return updated rows
      dimension by ( row_number() over (order by column_value) as key )
      measures     ( cast( column_value as varchar2(65) ) as string )
      rules
        upsert
        iterate( 6 )
          until ( presentv(string[iteration_number+2],1,0) = 0 )
        ( string[0] = string[0] || ',' || string[iteration_number+1] )
  ) string
from
  ( select group_key
    ,      cast( collect( DISTINCT val) as varchar2_table_type ) as string_table
    from t
    group by group_key
  ) ta
;
 

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 6    (null)
Group 7    (null)
Group 8    a,b
(null)     (null)
 

How it Works

The solution basically works the same as the Object Method solution. Instead of using a custom function to convert the nested table generated by COLLECT to a string however, the solution above uses a MODEL subquery similar to the one in MODEL Method 1 to do the conversion. See the MODEL Clause tutorial for an explanation of how the MODEL clause works.