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 stored procedure sp_dbcmptlevel

Msg 325, Level 15, State 1, Line 9 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 stored procedure sp_dbcmptlevel.

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 stored procedure sp_dbcmptlevel.

Why I’m getting this error?
The PIVOT command is not available for SQL Server 2000. When we upgrade the SQL Server 2000 instance to SQL Server 2005, the database COMPATIBILITY_LEVEL remain in 80 until we change it manually.

Solution:

If you are running SQL 2005
EXEC sp_dbcmptlevel 'myDatabaseName', 90

If you are running SQL 2008
EXEC sp_dbcmptlevel 'myDatabaseName', 100

New Syntax as of 02/26/2009:

ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

http://msdn.microsoft.com/en-us/library/bb510680.aspx