Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: CONCATENATE a field's values when GROUPING BY another field
"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 onlySearch google and ask tom for details. Received on Wed Jul 02 2003 - 13:51:58 CDT
![]() |
![]() |