SQL Server Time Series - a basic transformation

Question

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!

EAV tables are a design to be avoided: https://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/ — Kevin Cook, Jan 30 '15 at 22:17

Giorgos Betsos · Accepted Answer · 2015-01-30T19:02:31.643

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

SQL Server Time Series - a basic transformation

1 Answers1