Here's something for you to start with. To understand this method called CROSS TABS, you could visit this article: http://www.sqlservercentral.com/articles/T-SQL/63681/

To learn how to make it dynamic, use part 2: http://www.sqlservercentral.com/articles/Crosstab/65048/

WITH DayHours AS(

SELECT *

FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),

(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0))x(ihour)

)

SELECT ihour,

SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-02' THEN ClickCount ELSE 0 END) AS [2013-10-02],

SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-03' THEN ClickCount ELSE 0 END) AS [2013-10-03],

SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-04' THEN ClickCount ELSE 0 END) AS [2013-10-04],

SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-05' THEN ClickCount ELSE 0 END) AS [2013-10-05]

FROM clicks

RIGHT JOIN DayHours ON DATEPART( HH, RecordDay) = ihour

GROUP BY ihour