pivot, success

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- pivot, no success

declare @hwfg_t_Tracking table (operationId int primary key, hitTime datetime)
insert INTO @hwfg_t_Tracking values 
 ( 1,'2011-10-08 01:00'),( 2,'2011-10-08 02:00')
,(11,'2011-10-09 01:00')
,(21,'2011-10-10 01:00'),(22,'2011-10-10 02:00'),(23,'2011-10-10 03:00'),(24,'2011-10-10 04:00')
,(31,'2011-10-11 01:00'),(32,'2011-10-11 02:00'),(33,'2011-10-11 03:00'),(34,'2011-10-11 04:00')
,(41,'2011-10-12 01:00'),(42,'2011-10-12 02:00'),(43,'2011-10-12 03:00'),(44,'2011-10-12 04:00'),(45,'2011-10-12 05:00')
,(51,'2011-10-13 01:00'),(52,'2011-10-13 02:00'),(53,'2011-10-13 03:00'),(54,'2011-10-13 04:00')
,(61,'2011-10-14 01:00'),(62,'2011-10-14 02:00'),(63,'2011-10-14 03:00')

SELECT *
FROM (
     SELECT convert(varchar(10), hitTime, 20) AS d, operationId
     FROM   @hwfg_t_Tracking
     WHERE  hitTime >= '2011-10-08 00:00:00' AND hitTime < '2011-10-15 00:00:00'
     ) x
PIVOT (
     COUNT(operationId)
     FOR d IN ([2011-10-08], [2011-10-09], [2011-10-10]
              ,[2011-10-12], [2011-10-13], [2011-10-14])
     ) p

/*
SELECT cast(hitTime AS date) AS d, operationId
      ,cast('2011-10-08' AS varchar(10)) AS x
      ,convert(varchar(10), hitTime, 20) AS y
   FROM   @hwfg_t_Tracking
   WHERE  hitTime >= '2011-10-08 00:00:00' AND hitTime < '2011-10-15 00:00:00'
*/

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results