MySQL tutorial: column to row use group by and group_concat function

Mysql column to row – We can first group by the data set, and then associate the column data through the group_concat function to achieve the conversion from column to row.

For example:

We have the following table:

mysql> select * from t;                                                                                                                   +----+----------+--------+------+
| id | name     | status | age  |
+----+----------+--------+------+
|  1 | ylspirit |      1 |   22 |
|  2 | carol    |      1 |   21 |
|  3 | apple    |      1 |   19 |
|  4 | orange   |      2 |   22 |
|  5 | ben      |      2 |   23 |
|  6 | john     |      3 |   19 |
|  7 | jack     |      2 |   18 |
|  8 | han      |      1 |   17 |
|  9 | alin     |      4 |   25 |
| 10 | tom      |      1 |   18 |
+----+----------+--------+------+
10 rows in set (0.00 sec)

Need to get the following output:

+------+----------------+
| id   | names          |
+------+----------------+
|    1 | ylspirit, carol |
|    2 | orange, apple   |
|    3 | john, ben       |
|    4 | jack, han       |
|    5 | tom, alin       |
+------+----------------+

Group the data in table t according to every two persons.

Steps

First, use the CEILING() function to renumber the data in the table to generate a group id: gid

mysql> select ceiling(id / 2) as gid, name from t;

+------+----------+
| gid  | name     |
+------+----------+
|    1 | ylspirit |
|    1 | carol    |
|    2 | apple    |
|    2 | orange   |
|    3 | ben      |
|    3 | john     |
|    4 | jack     |
|    4 | han      |
|    5 | alin     |
|    5 | tom      |
+------+----------+

Then, use group by to group the data set according to the gid field, and use the GROUP_CONCAT() function to associate column data;

mysql> select GROUP_CONCAT(a.name SEPARATOR ', ') as names from (select ceiling(id / 2) as gid, name from t) as a group by a.gid;

+-----------------+
| names           |
+-----------------+
| ylspirit, carol |
| apple, orange   |
| ben, john       |
| jack, han       |
| alin, tom       |
+-----------------+

Finally, use variables to regenerate the result set id.

mysql> set @id = 0;
mysql> select @id := @id + 1 as id, GROUP_CONCAT(a.name SEPARATOR ', ') as names from (select ceiling(id / 2) as gid, name from t) as a group by a.gid;

+------+-----------------+
| id   | names           |
+------+-----------------+
|    1 | ylspirit, carol |
|    2 | orange, apple   |
|    3 | john, ben       |
|    4 | jack, han       |
|    5 | tom, alin       |
+------+-----------------+