MySQL 将行格式化为列

Question

我正在开发一个更新的内部网站，该网站将从 SQL 服务器提取数据。数据自动从 VAX 系统插入数据库。原始数据库如下所示:

mysql> SELECT * FROM userdb.clientdata WHERE datadate = '2016-09-23' AND header IN (1,3,6,9,212,2048);
+--------+--------------+-------------+----------+--------+-----------+-------------------------+---------------+---------------+
| id     | clientnumber | plantnumber | datahour | header | datavalue | datadate                | projectnumber | dec_precision |
+--------+--------------+-------------+----------+--------+-----------+-------------------------+---------------+---------------+
|  28673 |         NULL |           2 |        1 |      1 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28675 |         NULL |           2 |        1 |      3 | 78.368    | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28678 |         NULL |           2 |        1 |      6 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28681 |         NULL |           2 |        1 |      9 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  28884 |         NULL |           2 |        1 |    212 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  30720 |         NULL |           2 |        1 |   2048 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  30721 |         NULL |           2 |        2 |      1 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30723 |         NULL |           2 |        2 |      3 | 77.342    | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30726 |         NULL |           2 |        2 |      6 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30729 |         NULL |           2 |        2 |      9 | -5865.97  | 2016-09-23 00:00:00.000 |          NULL |             1 |
|  30932 |         NULL |           2 |        2 |    212 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |
|  32768 |         NULL |           2 |        2 |   2048 | 0         | 2016-09-23 00:00:00.000 |          NULL |             0 |

我需要能够对其进行格式化，以便结果如下所示:

+-------------------------+----------+---------+---------+---------+---------+-----------+------------+
| datadate                | datahour | header1 | header3 | header6 | header9 | header212 | header2048 |
+-------------------------+----------+---------+---------+---------+---------+-----------+------------+
| 2016-09-23 00:00:00.000 |        1 | -5865.97|  78.368 | -5865.97| -5865.97|         0 |          0 |
| 2016-09-23 00:00:00.000 |        2 | -5865.97|  77.342 | -5865.97| -5865.97|         0 |          0 |

如有任何帮助，我们将不胜感激。

Linux 版 MySQL 5.7.21

编辑:该网站将使用 php，但我正在寻找实际的 MySQL 查询来提供列出的结果，以便我可以将其存储在另一个表中。

Answer 1

您可以使用条件聚合在 MySQL 中进行透视:

SELECT datadate, datahour, 
       MAX(CASE WHEN header = 1 THEN datavalue END) as header1,
       MAX(CASE WHEN header = 3 THEN datavalue END) as header3,
       MAX(CASE WHEN header = 6 THEN datavalue END) as header6,
       MAX(CASE WHEN header = 9 THEN datavalue END) as header9,
       MAX(CASE WHEN header = 212 THEN datavalue END) as header212,
       MAX(CASE WHEN header = 2048 THEN datavalue END) as header2048
FROM userdb.clientdata
WHERE datadate = '2016-09-23' AND header IN (1, 3, 6, 9, 212, 2048)
GROUP BY datadate, datahour
ORDER BY datadate, datahour;