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 |
+------+-----------------+