Re: Help with SQL to concatenate row data ...

From: Richard Kuhler <noone_at_nowhere.com>
Date: Mon, 22 Apr 2002 18:27:16 GMT
Message-ID: <8CYw8.50512$VQ2.30758833@twister.socal.rr.com>

If you're going to use 9i, you might want to take a look at user-defined aggregate functions. See the thread "possible/impossible query?" on this newsgroup for an example I did to solve this same problem.

Richard Kuhler

Anti Spam wrote:
>
> I am hoping that someone can help me with a tip for a SQL problem that I
> have.
>
> Given an example table "MYCOLORS" which has the following 5 row entries:
>
> ID COLOR
> -- -----
> 1 VIOLET
> 1 INDIGO
> 2 BLUE
> 2 GREEN
> 2 YELLOW
> 3 ORANGE
>
> I would like to write SQL that produces the following output
>
> ID MIXED
> -- ------
> 1 VIOLET,INDIGO
> 2 BLUE,GREEN,YELLOW
> 3 ORANGE
>
> In other words, I am looking for a way to write a general SQL statement that
> aggregates the COLOR rows into single fields, grouped by the ID. Be advised
> that, beyond this example, I may not know how many different colors exist in
> the MYCOLORS table at any time.
>
> Can anyone help?
>
> Thanks,
> Charles
Received on Mon Apr 22 2002 - 13:27:16 CDT