How can I PIVOT TABLE Or CrossTab By Datetime?

• ppc493

  SSC Veteran

  Points: 290

  More actions

  November 12, 2012 at 10:20 am

  #265291

  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

  this code:

  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'

  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.

  i need output:

  EmpNo 10 11 12

  00001 07:22 07:13 07:54

  00002 07:30 07:34 07:18

  i'm begining learn pivot and crosstab. please help me.

  Thanks you for you time. 🙂

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  November 12, 2012 at 10:32 am

  #1558235

  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.

  According to the error, you don't have the database compatibility set correctly.

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg