MySQL - All entries with same timestamp in one row

Question

I recently build my first database with MySQL. I use it to log data from ten different sensors every ten minutes. Each sensor has a unique ID and all sensors are read out at the same time so that ten entries get the same timestamp. The database looks like this:

sensor_id |      timestamp      | sensor_value |
     1    | 2016-06-13 20:40:00 |      19.1    |
     2    | 2016-06-13 20:40:00 |      20.1    |
     3    | 2016-06-13 20:40:00 |      21.5    |
                      .
                      .
                      .
    10    | 2016-06-13 20:40:00 |      18.7    |
     1    | 2016-06-13 20:50:00 |      19.4    |
     2    | 2016-06-13 20:50:00 |      20.2    |
     3    | 2016-06-13 20:50:00 |      22.1    |
                      .
                      .
                      .
    10    | 2016-06-13 20:50:00 |      17.9    |
                      .
                      .
                      .

Now I would like to export the data in such a way that I get a row for each timestamp with ten following columns containing the values of the ten sensors:

                    |   1  |   2  |   3  | ... |   10 |
2016-06-13 20:40:00 | 19.1 | 20.1 | 21.5 | ... | 18.7 |
2016-06-13 20:50:00 | 19.4 | 20.2 | 22.1 | ... | 17.9 |
                               .
                               .
                               .

I tried to use GROUP_CONCAT and almost got what I was looking for. But this gives me all the sensor values in one column as a comma separated list

     timestamp      | GROUP_CONCAT(sensor_value) |
2016-06-13 20:40:00 |  19.1,20.1,21.5,...,18.7   |
2016-06-13 20:50:00 |  19.4,20.2,22.1,...,17.9   |
                          .
                          .
                          .

Unfortunately, sometimes one of the sensors fails to deliver its value and no entry is added into my database. Therefore, there are sometimes only nine values with the same timestamp. And the comma separated list can not tell me which of the sensors is missing. That is why I need one column per unique sensor ID. Is there a way to achieve this?

I tried to work it out by browsing Stack Overflow, but since I am fairly new to MySQL and databases I did not manage to resolve my problem without posting a new question. If it has been asked and answered before I am sorry and would be happy if someone redirected me in the right direction.

Thanks!

Possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Schleis, Jun 13 '16 at 18:56

score 0 · Answer 1 · answered Jun 13 '16 at 18:59

0

Just use conditional aggregation:

select timestamp,
       max(case when sensor_id = 1 then sensor_value end) as sensor_1,
       max(case when sensor_id = 2 then sensor_value end) as sensor_2,
       . . .
from t
group by timestamp;

answered Jun 13 '16 at 18:59

Gordon Linoff

1,242,037
58
646
786

MySQL - All entries with same timestamp in one row

1 Answers1