SQL Server语言 - 转换日为列，小时为行

Question

我正在寻找一种方法来转换行，以显示日期时间数据的日作为列和小时作为一个月的第一行。这是我一个月的数据示例:

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

我要的结果是

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

如何构建结果?

Answer 1

您可以使用Conditional Aggregate来完成此操作

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)