Re: concatenate strings in a query

> Sometimes I have to do reports that take a table like that (silly example):
>
> CREATE TABLE SILLY_EXAMPLE (
> KEY INTEGER,
> DATA VARCHAR2(4000)
> ) ;
>
> INSERT INTO SILLY_EXAMPLE VALUES (1,'AAA');
> INSERT INTO SILLY_EXAMPLE VALUES (1,'BBB');
> INSERT INTO SILLY_EXAMPLE VALUES (1,'CCC');
>
> INSERT INTO SILLY_EXAMPLE VALUES (2,'XXX');
> INSERT INTO SILLY_EXAMPLE VALUES (2,'YYY');
>
> and give an output like that:
>
> Key Data
> --- --------------
> 1 AAA,BBB,CCC
> 2 XXX,YYY
>
> I always solved using PL/SQL and a for..loop cursor with some logic
> inside the loop. I'm wondering if it's possible (with Oracle 9.0.1) to
> do the report with a single SQL query.

Yes, it is with user defined aggregate functions. See   http://www.adp-gmbh.ch/ora/sql/user_def_agg.html for an example

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch