mysql 行转列

在 MySQL 中，可以使用 `GROUP_CONCAT` 和 `CASE WHEN` 语句将行转列。

假设有如下的表 `table1`：

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
|  2 | D    | E    | F    |
+----+------+------+------+

可以使用如下的 SQL 语句将其行转列：

SELECT 
  id, 
  GROUP_CONCAT(CASE WHEN col_name = 'col1' THEN col_value END) AS col1,
  GROUP_CONCAT(CASE WHEN col_name = 'col2' THEN col_value END) AS col2,
  GROUP_CONCAT(CASE WHEN col_name = 'col3' THEN col_value END) AS col3
FROM 
  (SELECT id, 'col1' AS col_name, col1 AS col_value FROM table1
   UNION ALL
   SELECT id, 'col2' AS col_name, col2 AS col_value FROM table1
   UNION ALL
   SELECT id, 'col3' AS col_name, col3 AS col_value FROM table1) t
GROUP BY id;

运行结果如下：

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 | A    | B    | C    |
|  2 | D    | E    | F    |
+----+------+------+------+

这里的思路是将每列分别作为一行，然后用 `GROUP_CONCAT` 将同一行的数据连接起来。