How can I PIVOT TABLE Or CrossTab By Datetime?

Question

I need crosstab or pivot table By select Datetime.

Table filesTA

EmpNo     ChkDate                    ChkIn
00001     2012-10-10 00:00:00.000    2012-10-10 07:22:00.000
00002     2012-10-10 00:00:00.000    2012-10-10 07:30:00.000
00001     2012-10-11 00:00:00.000    2012-10-11 07:13:00.000
00002     2012-10-11 00:00:00.000    2012-10-11 07:34:00.000
00001     2012-10-12 00:00:00.000    2012-10-12 07:54:00.000
00002     2012-10-12 00:00:00.000    2012-10-12 07:18:00.000

I have tried following

SELECT tf.EmpNo,tf.ChkDate,tf.ChkIn
FROM (SELECT EmpNo,ChkDate,ChkIn
        ,ROW_NUMBER() OVER(PARTITION BY EmpNo ORDER BY ChkDate) as tfNum
        FROM filesTA) AS tf
    PIVOT(MIN(ChkDate) FOR tfNum IN ('2012-10-10'))
WHERE tf.ChkDate Between '2012-10-10' and '2012-10-12'

But getting following error

Incorrect syntax near 'PIVOT'. You may need to set the compatibility
level of the current database to a higher value to enable this feature.
See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Desired Output:

EmpNo     10     11     12
00001     07:22  07:13  07:54
00002     07:30  07:34  07:18

I'm beginning learn pivot and crosstab. please help me to get my query working.

What version of sql-server are you running? Compatibility level error would indicate you need to update the database compat level to at least 9 (2005)... — PinnyM, Nov 12 '12 at 17:22
You have not accepted/commented on bluefeet's answer. It is giving your desired output. Do you need anything else? — Sami, Nov 13 '12 at 09:39

Taryn · Accepted Answer · 2012-11-12T19:19:50.980

If you are not able to use the PIVOT function, then you can use an aggregate function with a CASE statement:

select empno,
  max(case when datepart(d, chkdate) = 10 
        then convert(char(5), ChkIn, 108) end) [10],
  max(case when datepart(d, chkdate) = 11 
        then convert(char(5), ChkIn, 108) end) [11],
  max(case when datepart(d, chkdate) = 12
        then convert(char(5), ChkIn, 108) end) [12]
from filesTA
where ChkDate Between '2012-10-10' and '2012-10-12'
group by empno

See SQL Fiddle with Demo

If you have access to PIVOT, then your syntax will be:

select empno, [10], [11], [12]
from
(
  select empno, datepart(d, chkdate) chkdate, 
    convert(char(5), ChkIn, 108) chkin
  from filesTA
) src
pivot
(
  max(chkin)
  for chkdate in ([10], [11], [12])
) piv

See SQL Fiddle with Demo

score 0 · Answer 2 · answered Nov 12 '12 at 19:07

IF you need to use PIVOT on databases with compatibility level below 90 it won't work.

Read this ALTER DATABASE Compatibility Level

After your modified DATABASE Compatibility Level your query will be look so

;WITH cte AS
 (
  SELECT EmpNo, CAST(ChkIn AS time) AS ChkIn, DATEPART(mm, ChkDate) as mm_ChkDate
  FROM filesTA
  WHERE ChkDate Between '2012-10-10' and '2012-10-12'
  )
SELECT EmpNo, [10], [11], [12] FROM cte  
PIVOT(
MIN(ChkIn) FOR cte.mm_ChkDate IN ([10], [11], [12])) x

How can I PIVOT TABLE Or CrossTab By Datetime?

2 Answers2