Mysql query: combine multiple rows in one row grouped by date

Question

I am collecting temperatures and humidity from 8 different sensors. Unfortunately I designed the table wrong and I use one row for each sensor measurement.

    id          sensor         date            temperature
100576           8  12/25/2020 18:29          38
100575           1  12/25/2020 18:29         17.2
100574           2  12/25/2020 18:29          5.8
100573           3  12/25/2020 18:29         -9.2
100572           7  12/25/2020 18:29         14.9
100571           6  12/25/2020 18:29         16.6
100570           5  12/25/2020 18:29         -8.9
100569           4  12/25/2020 18:29         19.8
100568           8  12/25/2020 18:28          39
100567           1  12/25/2020 18:28         17.2
100566           2  12/25/2020 18:28          5.8
100565           3  12/25/2020 18:28         -9.2
100564           7  12/25/2020 18:28          15
100563           6  12/25/2020 18:28         16.6
100562           5  12/25/2020 18:28         -8.8
100561           4  12/25/2020 18:28         19.8

I would like to write a mysql query with all sensors in one row grouped by date

  date             sensor1    sensor2    sensor3    sensor4    sensor5    sensor6    sensor7    sensor8
12/25/202018:28      17.2       5.8        -9.2       19.8       -8.9       16.6       14.9        39
12/25/202018:29      17.2       5.8        -9.2       19.8       -8.8       16.6        15         39

Is there an easy way to do this ? Thank you !

Answer 1

To pivot the dataset for display, use conditional aggregation:

select date,
    max(case when sensor = 1 then temperature end) as sensor1,
    max(case when sensor = 2 then temperature end) as sensor2,
    ...
from mytable
group by date

NB: your design is the right way to store your data; each sensor value is stored in a separate row, as it should be.