How can I PIVOT TABLE or CrossTab by date?

Question

I need a crosstab or pivot table. Choose Datetime.

FilesTA table

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 tried to follow

 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 the 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 Result:

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

I'm starting to learn rods and crosstabs. help me make my request work.

Answer 1

If you cannot use the PIVOT function, you can use the aggregate function with the 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 look like this:

 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

Answer 2

IF you need to use PIVOT in databases with a compatibility level below 90, this will not work.

Read This Compatibility Level ALTER DATABASE

After your modified DATABASE compatibility level, your query will look like this

 ;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