On the collect aggregate function

A feature that seems to be missing in Oracle SQL seems to be the concatenation of strings within a group by-group.

Consider the following table with its data:

create table collect_example (
  id   number,
  foo  varchar2(20)
);

insert into collect_example values (1, 'Honda');
insert into collect_example values (1, 'Audi');
insert into collect_example values (1, 'Mercedes');
insert into collect_example values (2, 'Apple');
insert into collect_example values (2, 'Pear');
insert into collect_example values (3, 'New York');
insert into collect_example values (3, 'Boston');
insert into collect_example values (3, 'Berlin');
insert into collect_example values (3, 'Jakarta');

Now, say, someone wants to do something like:

select
  id,
  concatenate(foo)
from
  collect_example
group by
  id;

This will of course not work!

However, Oracle 10g comes with the collect aggregate function that can be used achieve the desired goal.

In order to use collect, a type must be created:

create type varchar2_t as table of varchar2(20);
/

Also, a function, that receives a parameter of this type, is needed. The function will actually concatenate the single varchar2s passed through this parameter:

create or replace function collect_func (t in varchar2_t) 
  return varchar2
as 
  ret varchar2(2000) := '';
  i   number;
begin
  i := t.first;
  while i is not null loop
    if ret is not null then
      ret := ret || ' - ';
    end if;

    ret := ret || t(i);

    i := t.next(i);
  end loop;

  return ret;
end;
/

Now, that everything is here, the select statement can be formulated like so:

column concatenated format a50

select
    id,
    collect_func(cast(collect (foo) as varchar2_t)) concatenated
  from 
    collect_example
  group by 
    id;

And here's the output:

        ID CONCATENATED
---------- --------------------------------------------------
         1 Honda - Audi - Mercedes
         2 Apple - Pear
         3 New York - Boston - Berlin - Jakarta

See also: user defined aggregate functions.

Update September 22, 2005

Here's an article on the missing product() aggregate function.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger