UNPIVIOT AND RANK the data

Question

I have a table with 24 hrs data, i want to pivot the data and assign a rank for them based on the counts.

create table #HourlyData_Counts
(Hr_1 int,Hr_2 int ,Hr_3 int,Hr_4 int,Hr_5 int ,Hr_6 int ,Hr_7 int , Hr_8 int ,Hr_9 int , Hr_10 int )

insert into #HourlyData_Counts
values (55,89,78,77,67,99,45,33,23,91)

select * from #HourlyData_Counts

--drop table #HourlyData_Counts

required output

Answer 1

You will need to use Unpivot in this case.

select Hours,counts,Ranks=Row_Number()over(order by counts)
from 
(
  select * from #HourlyData_Counts
) src
UNPIVOT
(
  counts For Hours in(Hr_1,Hr_2,Hr_3,Hr_4,Hr_5,Hr_6,Hr_7,Hr_8,Hr_9,Hr_10) 
) unpiv;