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 and a custom function 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)

 

The function is created as follows.

create function nested_table_to_csv
( p_table     in varchar2_table_type ,
  p_delimiter in varchar2 default ','
)
return varchar2
is

  ----------------------------------------------------------------------
  -- Usage:
  --
  -- p_table
  --   - contains the table you wish to convert to a string
  --
  -- p_delimiter
  --   - contains the varchar2 string you want to see between each
  --     value in the returned string
  --   - default value is ',' (comma)
  --   - set it to null if you don't want any delimiters
  --   - e.g. p_delimiter => '/' returns
  --
  --       a/b/c/d
  ----------------------------------------------------------------------

  i        binary_integer  := p_table.first ;
  v_return varchar2(32767) := null ;

begin

  while i is not null
  loop

    if i = p_table.first then
      v_return := p_table(i);
    else
      v_return := v_return || p_delimiter || p_table(i);
    end if;

    i := p_table.next(i) ;

  end loop;

  return( v_return );

end nested_table_to_csv;
/

show errors
No errors.
 

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 and function we created above in conjunction with COLLECT .

clear breaks

select
  group_key ,
  NESTED_TABLE_TO_CSV
    ( cast( COLLECT(val) as VARCHAR2_TABLE_TYPE )
    ) 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,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, unlike other solutions we have seen so far, 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, since COLLECT returns a nested table which is, by definition, an unordered set of data we cannot guarantee the values in our delimited string will appear in any given order. If you need ordered values you can add sorting logic to the NESTED_TABLE_TO_CSV function.

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 ,
  nested_table_to_csv
    ( cast( collect( DISTINCT val ) as varchar2_table_type )
    ) 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
Group 5    a,b,d,e
Group 6    (null)
Group 7    (null)
Group 8    a,b
(null)     (null)