Pivot a table and display n ordered time series

Question

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?

Bernd Buffen · Accepted Answer · 2015-10-26T18:27:47.067

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)

@Adarsh Gangadharan - I have add a Version in my Answer for dynamic device ids. — Bernd Buffen, Oct 26 '15 at 18:28
How can I convert this into a stored procedure. I tried. But instead of returning the results, it returns the query. — hirani89, Sep 21 '20 at 11:38
see: https://stackoverflow.com/questions/5895383/mysql-prepare-statement-in-stored-procedures — Bernd Buffen, Sep 21 '20 at 13:00

Pivot a table and display n ordered time series

1 Answers1

Linked