In SQL, is there a way to turn a table into a list?

Question

I have tables that look like this:

mysql> select id, name from wp_bp_groups limit 5;
+----+-----------------------------------------+
| id | name                                    |
+----+-----------------------------------------+
|  3 | Some Group Here                         |
| 11 | Another Group Here                      |
| 29 | Groupy Groupy Groupy Group              |
| 39 | Some Other Other Group Goes Here        |
|  7 | Yep, Here's a Test Group                |
+----+-----------------------------------------+
5 rows in set (0.02 sec)

mysql> select group_id, user_id from wp_bp_groups_members limit 5;
+----------+---------+
| group_id | user_id |
+----------+---------+
|       17 |      71 |
|       24 |     209 |
|       22 |     175 |
|       17 |     200 |
|       17 |     102 |
+----------+---------+

Eventually, I want to get an array that lists all the members in a group, so I have a SQL query that joins them, like this:

SELECT wp_bp_groups.name, wp_bp_groups_members.user_id FROM wp_bp_groups LEFT JOIN wp_bp_groups_members ON wp_bp_groups.id = wp_bp_groups_members.group_id;

Which outputs something like this:

mysql> SELECT wp_bp_groups.name, wp_bp_groups_members.user_id from wp_bp_groups left join wp_bp_groups_members on wp_bp_groups.id = wp_bp_groups_members.group_id limit 5;
+------------------+---------+
| name             | user_id |
+------------------+---------+
| Test Group       |       1 |
| Test Group       |     206 |
| Test Group       |      24 |
| Test Group       |      47 |
| Test Group       |      52 |
+------------------+---------+
5 rows in set (0.02 sec)

But I'd ideally like it to return something more like this:

name: Test Group
user_ids: 1, 206, 24, 47, 52

Is there a way to do that?

be careful with `GROUP_CONCAT` because it has a maximum length that's not so big http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — Luca Rainone, Nov 12 '14 at 15:07

score 3 · Accepted Answer · answered Nov 12 '14 at 15:12

In MySQL you can use function group_concat which will make concatenation of all elements in each group. So you have to group your results. As you want to get all users ids you can query

SELECT wp_bp_groups.name, GROUP_CONCAT(wp_bp_groups_members.user_id) as user_ids FROM wp_bp_groups LEFT JOIN wp_bp_groups_members ON wp_bp_groups.id = wp_bp_groups_members.group_id group by wp_bp_groups.name;

which should results as

+------------------+----------------------+
| name             | user_ids             |
+------------------+----------------------+
| Test Group       |       1,206,24,47,52 |
+------------------+----------------------+

In SQL, is there a way to turn a table into a list?

1 Answers1