Kukuh Heru Irawan Kukuh Heru Irawan - 1 month ago 11
SQL Question

SQL Server - Convert Days as Column, Hour as row

I'm looking for a way to convert rows to display datetime data's day as column and hour as first row in one month.
This is my example data in a month:

Time | Value
--------------------------------
2016-10-01 00:00:00 | 23
2016-10-01 00:30:00 | 35
..... | .....
2016-10-31 23:30:00 | 52


The result i want is

Time | 1 | .... | 31 | <-- Day 1 - 31
---------------------------------------
00:00:00 | 23 | .... | .... |
00:30:00 | 35 | .... | .... |
... | .... | .... | .... |
23:30:00 | .... | .... | 52 |


How can i build the result?

Answer

You can use Conditional Aggregate to do this

select cast([Time] as time),
       Max(case when DAY([Time]) = 1 then Value end) As [1]
       Max(case when DAY([Time]) = 2 then Value end) As [2]
       Max(case when DAY([Time]) = 3 then Value end) As [3]
       .....
       Max(case when DAY([Time]) = 31 then Value end) As [31]
From yourtable
Where Month([Time]) = 10 -- pass the required month  
Group by cast([Time] as time)