'Split on table column into two columns in a view

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

sqlsql-serverview


Solution 1:[1]

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)

Solution 2:[2]

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]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Andrew
Solution 2 KeithL

Related Questions