Split on table column into two columns in a view-CodePudding

Picture of table

Hello I have trouble creating a view in SQL. I need the values for SensorID = 1 in one column the values for SensorID = 2 in the other column.

|Cels | Fah |
|5    | 41  |
|5    | 41  |

I have tried the following script but it doenst let me have two different WHERE statements.

CREATE VIEW myView as
Select Value as [Cels], Value as [Fah]
FROM LOG
WHERE SensorId = 1, SensorId = 2

How can I archeive this

CodePudding user response：

Simple case statement with aggregation -

select
max(case when sensorid = 1 then value else null end) as cels,
max(case when sensorid = 2 then value else null end) as fah
from
log
where
sensorid in (1,2)

CodePudding user response：

I think you want something like this:

select
  [timestamp],
  max(case when sensorid = 1 then value else null end) as cels,
  max(case when sensorid = 2 then value else null end) as fah
from
  log
where
  sensorid in (1,2) 
group by
  [timestamp]