I'm using SQL Server 2008
I have a table MainTable with 3 columns Date, Parameter, Value.
Sample data:
Date Parameter Value
-------------+--------------+-------
01-Jan-2010 | Temperature | 30
01-Jan-2010 | SnowLevel | 2
01-Jan-2010 | Humidity | 60
02-Jan-2010 | Temperature | 32
02-Jan-2010 | SnowLevel | 5
Now I want to write a query to transform this time series data into the following table having a separate column for each parameter:
Date, Temperature, SnowLevel, Humidity
with the following data:
Date Temperature SnowLevel Humidity
------------+----------------+-------------+----------
01-Jan-2010 | 30 | 2 | 60
02-Jan-2010 | 32 | 5 | NULL
I know that SQL Server has a lot of functionality for working with time series, but I couldn't find any basic functionality that would make this transformation. I found tens of articles about data prediction with DMX, but no one about this basic stuff.
A direct approach would be to join this table with itself for every parameter(/column), but I have hundreds of different parameters and definitely this approach wouldn't work in my case.
Any help is very much appreciated!
What you want is typically achieved using PIVOT:
SELECT [Date], [Temperature], [SnowLevel], [Humidity]
FROM (
SELECT [Date], Parameter, Value
FROM #MainTable )src
PIVOT (
MAX(Value)
FOR Parameter IN ([Temperature], [SnowLevel], [Humidity])
) pvt
The above query produces the following output based on the sample data of the OP:
Date Temperature SnowLevel Humidity
---------------------------------------------
2010-01-01 30 2 60
2010-01-02 32 5 NULL