SQL – Transpose query data with additional rows added in [closed]

I have come once more to seek your guidance. I am not actually sure how possible this is, but I can only hope.

I have an already heavily joined and aggregated query which pulls out results as shown in the attached picture.

As mentioned, this is an already heavily editing query with multiple joins and aggregates to take half hour data and sum it hourly.

I am wondering if it is at all possible to now transpose the data so that there are 24 seperate hourly entries for each date?

So instead of having one single line for 2014-01-01 with each hourly reading in a seperate column, is it at all possible to change it so there are just two columns. One for date/time and one with the hourly total.

So it would look more like:

and then continue down for each hour of each day.

I am using SQL Server 2014 if that helps.

Thank you in advance!

Answer

Assuming your time columns are named time0, time1, …, time23 you could write

SELECT DateAdd(HOUR, 0, date) as DateTime, time0 as total from table
UNION
SELECT DateAdd(HOUR, 1, date) as DateTime, time1 as total from table
UNION
...
SELECT DateAdd(HOUR, 23, date) as DateTime, time23 as total from table

Advertisement

Answer