DigitalMandrake DigitalMandrake - 11 days ago 6
MySQL Question

MySQL select distinct not getting desired query result

I have a database table with the following data:

+--------------------+-----------------------------+
| order_no | part |
+--------------------+-----------------------------+
| AAAAAAAAAAAAA | QQQQQQQQ-WWW |
| AAAAAAAAAAAAA | EEEEEEEE-TTT |
| BBBBBBBBBBBBB | 33333333-333 |
| BBBBBBBBBBBBB | 44444444-444 |
| BBBBBBBBBBBBB | EEEEEEEE-TTT |
+--------------------+-----------------------------+


My problem is that I can't make a query to produce this result:

+----------------+-------------------------------------------+
| order_no | part |
+----------------+-------------------------------------------+
| AAAAAAAAAAAAA | QQQQQQQQ-WWW, EEEEEEEE-TTT |
| BBBBBBBBBBBBB | 33333333-333, 44444444-444, EEEEEEEE-TTT |
+----------------+-------------------------------------------+


Thanks in advance.

Answer

The group_concat aggregate function is just what the doctor ordered:

SELECT   order_no, GROUP_CONCAT(part SEPARATOR ', ')
FROM     mytable
GROUP BY order_no
Comments