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

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

Update September 22, 2005