Re: CONCATENATE a field's values when GROUPING BY another field

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Wed, 2 Jul 2003 11:51:58 -0700
Message-ID: <iYFMa.7$eD3.39@news.oracle.com>

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F030426.4784F185_at_exxesolutions.com...
> "Vincent C." wrote:
>
> > Hello all,
> >
> > I have a tricky SQL question. The following query
> >
> > select username, expertise_code from exp_expertise where username =
> > 'francesco';
> >
> > returns the 5 lines below:
> >
> > USERNAME EXPERTISE_CODE
> > ----------------------- --------------
> > francesco A02
> > francesco G
> > francesco G05
> > francesco A11
> > francesco A03
> >
> > But I'd rather have only one line like this:
> >
> > USERNAME EXPERTISE_CODE
> > ----------------------- --------------
> > francesco A02,G,G05,A11,A03
> >
> > In other words, I would like to "GROUP BY username" and find a way
> > (with some aggregate function?) to concatenate the values for the
> > EXPERTISE_CODE field (using a separator like a comma).
> >
> > Any idea?
> >
> > Thanks a lot.
> > Vincent
>
> 2. Use a cursor loop.

The disadvantage is that you have to write application specific code that can't be reused.

You can program aggregate functions generically with the following methods:

1. 9i user defined aggregates
2. 8i collections
3. table functions and refcursor
4. sys_connect_by_path -- that one for string concatenation only

Search google and ask tom for details. Received on Wed Jul 02 2003 - 13:51:58 CDT