Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.chReceived on Tue Jan 27 2004 - 16:24:13 CST