Summarizing a column to a DISTINCT value - MySQL

[worldcyclist@gmail.com]

Hey folks,
I am just getting into MySQL and have run into a problem with
subqueries.
I am running MySQL 4.0.21 and I am trying to CONCAT and I have no idea
where I'm tripping up...

The data looks like this....

+----+------------+---------+
| id | product | color |
+----+------------+---------+
| 1 | mixer | blue |
| 2 | mixer | green |
| 3 | mixer | yellow |
+----+------------+

I need to generate this....
+----+------------+---------+
| id | product | color |
+----+------------+---------+
| 1 | mixer | blue/green/yellow |
+----+------------+

In short, for each distinct product, generate a color field where the
all the choices are concatenated by a slash.

My apologies if this is remarkably simple. The real world query is
actually more brutal than this one which I have reduced for simplicity.

Sherabg

[Bill]

com wrote: 

You need MySQL 4.1 to do this. The solution is:

SELECT MIN(id), product,
GROUP_CONCAT(color ORDER BY color SEPARATOR '/')
FROM tablename
GROUP BY product;

See doentation on GROUP_CONCAT() here:
http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

There is no alternative for GROUP_CONCAT() in MySQL 4.0. If you can't
upgrade, you'll have to fetch all the data and do the grouping in
application code.

Regards,
Bill K.