SQL data in one field separated by coma

Question

Possible Duplicate:
Can I concatenate multiple MySQL rows into one field?

I have three tables items, sizes and item_sizes for many to many relationship with join i can query this:

item     size
shirt    L
shirt    XL
dress    S
dress    L
dress    XL

But i want this:

item     size
shirt    L, XL
dress    S, L, XL

Speed doesn't matter i want only the results.I can do it with while loop but is there another way of doing this query?

Answer 1

select item, group_concat(size)
from the_table
group by item;

More details in the manual: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Answer 2

Try GROUP_CONCAT()

SELECT item, GROUP_CONCAT(size)
FROM table_name
GROUP BY item

Answer 3

Query:

SQLFIDDLEEXample

SELECT item,
       group_concat(size separator ', ') AS SIZE
FROM Table1
GROUP BY item

Result:

|  ITEM |     SIZE |
--------------------
| dress | S, L, XL |
| shirt |    L, XL |