sql - 如何按日期时间进行数据透视表或交叉表？

Question

我需要交叉表或数据透视表，通过选择日期时间。

表格文件TA

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

我尝试过以下

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'

但是出现以下错误

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.

所需输出:

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

我开始学习数据透视表和交叉表。请帮助我让我的查询正常工作。

Answer 1

如果您无法使用 PIVOT 函数，则可以使用带有 CASE 语句的聚合函数:

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

参见SQL Fiddle with Demo

如果您有权访问PIVOT，那么您的语法将是:

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

参见SQL Fiddle with Demo