Pivot Data

[Jamfool]

dt Requests
19/09/2004 10:00:04 10
20/09/2004 07:00:06 161548
21/09/2004 00:00:07 164190
21/09/2004 12:00:07 100000
21/09/2004 12:20:07 100000
19/09/2004 01:00:06 155289


Can someone help me pivot the date in this wait table.

I need it in the following format:

Sum requests as the data broken down per hour in row and per day across the columns.

Date--> 19/09/2004 20/09/2004 21/09/2004
Hours
0
1
2
3
4
5
6
7
8
9
10
11
12
..etc to 23


Thanks

 

[amrita418]

Is there a limited number of days you expect the get the data for? I mean is there a limit on the dates you want to be displayed or is it supposed to be dynamic?

If it is static, for example to list 3 days worth of data
Try this:

select 		datediff(hour, convert(varchar, dt, 101), dt) 'Date Hours', 
		sum(case when convert(varchar, dt, 101) = '09/19/2004' then requests else 0 end) '09/19/2004',
		sum(case when convert(varchar, dt, 101) = '09/20/2004' then requests else 0 end) '09/20/2004',
		sum(case when convert(varchar, dt, 101) = '09/21/2004' then requests else 0 end) '09/21/2004'
from 		#TableA
group by 	datediff(hour, convert(varchar, dt, 101), dt)

Regards,
AA