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:…

Accepted Answer

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

How can I PIVOT TABLE Or CrossTab By Datetime?

Solution

Problem