Transpose two rows into single row (mySql)

Question

in MySql I have this kind of table (datas):

What I want to do is to get date, temperature for sensor (1) , temperature for sensor (2) in a single row (group by date).

Now I have two records for the same dtg for sensor 1 and sensor 2...

Thank you

This also looks like you're using a DS18S20 on (I'm going to stab in the dark) a RPi. I've just been doing the same thing, and have a bit of code for generating time-series data that might help you would like - https://gist.github.com/calcinai/801e4e805139cd18278e45c5da090340 — calcinai, Sep 27 '16 at 08:31

Tim Biegeleisen · Answer 1 · 2016-09-27T08:12:10.860

2

Use GROUP_CONCAT:

SELECT dtg, GROUP_CONCAT(temperature ORDER BY sensor_id)
FROM yourTable
GROUP BY dtg

Demo here:

SQLFiddle

edited Sep 27 '16 at 08:12

answered Sep 27 '16 at 08:05

Tim Biegeleisen

502,043
27
286
360

Ok but "group_concat" will concatenate two values separated by coma? is there a way to get atribute for each sensor? For example (dtg,temp1,temp2)? – Ferguson Sep 27 '16 at 08:19
`separated atribute for each sensor` ... what do you mean by this? – Tim Biegeleisen Sep 27 '16 at 08:20
Now I have dtg atribute and (temp1,temp2) as single atribute.. what I want is to get dtg,temp1,temp2 (if possible) – Ferguson Sep 27 '16 at 08:22
2

What you're talking about is transposing columns. This isn't possible with mysql out of the box, but if you knew the `sensor_id`s beforehand, you can do things like `SELECT COALESCE(IF(sensor_id='x', temperature, NULL)) sensor_x, COALESCE(IF(sensor_id='y', temperature, NULL)) sensor_y` – calcinai Sep 27 '16 at 08:27

score 1 · Answer 2 · edited May 23 '17 at 12:15

1

You can use a self join:

SELECT 
    a.dtg AS dtg1,
    a.sensor_id AS sensor_id1,
    a.temperature AS temperature1,
    b.sensor_id AS sensor_id2,
    b.temperature AS temperature2
FROM yourTable AS a
LEFT JOIN yourTable AS b 
    ON a.dtg = b.dtg
    AND NOT a.sensor_id = b.sensor_id
GROUP BY a.dtg

edited May 23 '17 at 12:15

Community

1
1

answered Sep 27 '16 at 08:24

Kaii

20,122
3
38
60

Kaii with your select statement I get duplicated values... http://s5.postimg.org/4mkm8my2v/screenshot_1236.jpg – Ferguson Sep 27 '16 at 08:31

calcinai · Answer 3 · 2016-09-29T06:42:23.337

It's not ideal hardcoding these values in the query, but this is a workaround to transpose the rows to columns:

SELECT dtg,
    COALESCE(IF(sensor_id='28-000004a9fa09', temperature, NULL)) t1, 
    COALESCE(IF(sensor_id='28-000004aa21cd', temperature, NULL)) t2
FROM readings
GROUP BY dtg

You might need to play with the aggregates if you start getting unpredictable time readings. Especially given the DB18B20s can sometimes take a while to read, it's possible you might get the seconds overlapping.

Transpose two rows into single row (mySql)

3 Answers3

SQLFiddle