my sql调用数据转置
时间: 2023-09-10 22:12:58 浏览: 42
你可以使用MySQL内置函数`GROUP_CONCAT`和`CONCAT`来实现数据转置。
假设你有如下的表结构:
```
+----+-------+-------+-------+
| id | col1 | col2 | col3 |
+----+-------+-------+-------+
| 1 | val11 | val12 | val13 |
| 2 | val21 | val22 | val23 |
| 3 | val31 | val32 | val33 |
+----+-------+-------+-------+
```
如果你想将该表按列转置,可以使用如下的SQL语句:
```
SELECT
CONCAT('col', t.col_num) AS column_name,
GROUP_CONCAT(t.col_value ORDER BY t.id) AS column_values
FROM
(SELECT 1 AS col_num, col1 AS col_value, id FROM table_name
UNION ALL
SELECT 2 AS col_num, col2 AS col_value, id FROM table_name
UNION ALL
SELECT 3 AS col_num, col3 AS col_value, id FROM table_name) AS t
GROUP BY t.col_num;
```
这将返回以下结果:
```
+-------------+---------------------------+
| column_name | column_values |
+-------------+---------------------------+
| col1 | val11,val21,val31 |
| col2 | val12,val22,val32 |
| col3 | val13,val23,val33 |
+-------------+---------------------------+
```
这样,你就成功将表按列转置了。