In MySQL How do I SELECT all ids from rows with a similar value

Question

I want to pull all the unique IDs for particular rows with the same username and then display each result as a row.

For example...

Here's my table:

+----+------+
| id | name |
+----+------+
| 1  | Joe  |
| 2  | Amy  |
| 3  | Joe  |
| 4  | Amy  |
| 5  | Joe  |
| 6  | Amy  |
+----+------+

Here's the result I want:

+------+-------+
| name | ids   |
+------+-------+
| Joe  | 1,3,5 |
| Amy  | 2,4,6 |
+------+-------+

How do I pull this result in MySQL?

Answer 1

Use a GROUP_CONCAT() with DISTINCT clause to aggregate unique ids for a particular name:

SELECT name, GROUP_CONCAT(DISTINCT id SEPARATOR ',') AS ids
FROM yourtable
GROUP BY name

To review the usage of it also see MySQL group_concat with select inside select.