CODEWITHSUNDEEP
sqlsql-server
Jomet-abc
Jomet-abc

Reputation: 45

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

I have got 2 solutions from here? Do anyone have any idea about which is faster and best?

Upvotes: 0

Views: 35

Answers (2)

TEXHIK
TEXHIK

Reputation: 1398

Try this:

WITH temperature (sID, rTime, temp)
AS (SELECT sensorid, reading_time, value FROM data_table WHERE type=0)
SELECT sensorid, reading_time, temp, value as humidity
FROM data_table JOIN temperature ON sensorid=sID AND reading_time=rTime
WHERE type=1

Explanation:

  • WITH create temporary table for temperature with all temperature rows
  • Tables JOIN by time and sensor
  • Selecting temperature from temporary table and humidity from original table for each sensor-time pair

Upvotes: 1

sstan
sstan

Reputation: 36523

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

Upvotes: 2

Related Questions