You can use Dynamic CROSS-TABS to achieve this

You can have a look at the below mentioned articles for more details on CROSS-TABS

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

Something like below

DECLARE@strColumns VARCHAR(MAX)

DECLARE@strSQL VARCHAR(MAX)

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL

DROP TABLE #tmp_table

CREATE TABLE #tmp_table

(

CallDateTimeDATETIME,

HistIDINT

)

INSERT #tmp_table( CallDateTime, HistID )

SELECT'2013-02-01 16:14:41.000', 2671058 UNION ALL

SELECT'2013-02-01 17:02:15.000', 2671057 UNION ALL

SELECT'2013-02-01 17:06:41.000', 2671059 UNION ALL

SELECT'2013-02-01 17:07:20.000', 2671069 UNION ALL

SELECT'2013-02-01 17:22:57.000', 2671068 UNION ALL

SELECT'2013-02-01 17:38:15.000', 2671067 UNION ALL

SELECT'2013-02-01 17:42:48.000', 2671066 UNION ALL

SELECT'2013-02-01 17:53:47.000', 2671065 UNION ALL

SELECT'2013-02-01 18:02:37.000', 2671064 UNION ALL

SELECT'2013-02-01 08:07:04.000', 2671063 UNION ALL

SELECT'2013-02-01 08:11:09.000', 2671062 UNION ALL

SELECT'2013-02-01 08:22:12.000', 2671061 UNION ALL

SELECT'2013-02-01 08:22:48.000', 2671060 UNION ALL

SELECT'2013-02-02 08:22:48.000', 2671060

SELECT@strColumns = COALESCE( @strColumns + ', ', '' )

+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) < 30 THEN 1 ELSE 0 END ) AS ['

+ CAST( 8 + Numbers.number AS VARCHAR(3) ) + '_' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + '30], ' + CHAR(13)

+ ' SUM( CASE WHEN DATEPART( HOUR, CallDateTime ) = ' + CAST( 8 + Numbers.number AS VARCHAR(3) ) + ' AND DATEPART( MINUTE, CallDateTime ) >= 30 THEN 1 ELSE 0 END ) AS ['

+ CAST( 830 + Numbers.number * 100 AS VARCHAR(4) ) + '_' + CAST( 9 + Numbers.number AS VARCHAR(3) ) + ']' + CHAR(13)

FROM(

SELECTnumber

FROMmaster..spt_values

WHEREtype = 'P' AND number < 12

) Numbers --===You can use a Tally table instead of this if you have one

SET@strSQL = ' SELECTCONVERT(VARCHAR(20), CallDateTime, 110) AS Date, '

+@strColumns

+ ' FROM#tmp_table '

+ ' GROUP BY CONVERT(VARCHAR(20), CallDateTime, 110) '

EXECUTE( @strSQL )

IF OBJECT_ID('tempdb..#tmp_table') IS NOT NULL

DROP TABLE #tmp_table

Edit: Added code as an example