Help with SQL to concatenate row data ...

From: Anti Spam <nomorespam_at_please.com>
Date: Sun, 21 Apr 2002 13:53:44 GMT
Message-ID: <Ivzw8.394663$2J2.21795703@typhoon.southeast.rr.com>

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 Sun Apr 21 2002 - 08:53:44 CDT