I have a table named "readings" which contains data from sensors with the following columns:
id id_device date value
1 1 2015-01-01 00:00:00 0.2
2 2 2015-01-01 00:00:00 0.7
3 1 2015-01-01 00:00:10 0.3
4 2 2015-01-01 00:00:10 0.8
5 1 2015-01-01 00:00:20 0.4
6 2 2015-01-01 00:00:20 0.9
And I want to transform it to this table in a single query:
date device_id_1 device_id_2
2015-01-01 00:00:00 0.2 0.7
2015-01-01 00:00:10 0.3 0.8
2015-01-01 00:00:20 0.4 0.9
I've found that doing this is called "pivoting a table" but I've only found how to sum values, not how to order them in time series
I've tried
SELECT DISTINCT(date) FROM readings
and then inside the results loop, querying again for each sensor
SELECT value FROM readings WHERE date=$date AND id_device=$id
But I'm sure that this is not very efficient because it is a lot of queries (one query for each value)
How the sql query would be like?
Try this:
SELECT r.id,
r.`date`,
GROUP_CONCAT(IF(r.device_id = 1,r.`VALUE`,NULL)) AS device_id_1,
GROUP_CONCAT(IF(r.device_id = 2,r.`VALUE`,NULL)) AS device_id_2
FROM readings r
GROUP BY r.`DATE`
ORDER BY r.`DATE` ASC;
+----+---------------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 |
+----+---------------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 |
+----+---------------------+-------------+-------------+
2 rows in set (0.00 sec)
And here a Version for dynamic device_id's
SELECT group_concat(
"GROUP_CONCAT(IF(device_id = ",
device_id,",`value`,NULL)) AS device_id_",
device_id
SEPARATOR ',\n') INTO @sql_mid
FROM (SELECT DISTINCT device_id FROM readings ORDER BY device_id) AS r
ORDER BY device_id;
SELECT CONCAT("SELECT id, `date`, ", @sql_mid, " FROM readings GROUP BY `DATE` ORDER BY `DATE` ASC") INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
It generates all Columns
+----+---------------------+-------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 | device_id_3 |
+----+---------------------+-------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 | NULL |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 | NULL |
| 5 | 2015-10-01 10:11:00 | NULL | NULL | 9.9 |
+----+---------------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)
