Jomet-abc Jomet-abc - 5 months ago 8
SQL Question

SQL to Combine 2 Column value to Rows

I have data like,

ID SensorID Reading_Time Value Type
11 A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:16.4980000 -04:00 24.782219 0
12 A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:16.4980000 -04:00 23.121845 1
13 A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:46.5780000 -04:00 24.77972 0
14 A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:46.5780000 -04:00 23.121845 1


Type 0 for temp and 1 for Humidity
I have no clue . how to write sql to get result combined like

SensorId Reading_Time Temp Humidity
A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:16.4980000 -04:00 24.782219 23.121845
A3C3EB0B-1F5E-3737-B5F7-495BFA238F88 2016-06-02 14:38:46.5780000 -04:00 24.77972 23.121845

Answer

One way to do it is using conditional aggregates, which is a good way to make this work with any database.

select sensorId,
       reading_time,
       min(case when type = 0 then value end) as Temp,
       min(case when type = 1 then value end) as Humidity
  from tbl
 group by sensorId, reading_time
Comments