Unhelpful error message when doing pivot

Question

When I write the following query:

SELECT id,status
FROM dbo.View_Request
PIVOT ( COUNT(id) FOR status IN([CL],[HOLD])) AS pvt

I get a vague error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

What exactly am I doing wrong?

Answer 1

Your database is presumably at SQL Server 2000 compatibility level. You need to fix that then you will get these other error messages instead.

Msg 207, Level 16, State 1, Line 5
Invalid column name 'id'.
Msg 207, Level 16, State 1, Line 5
Invalid column name 'status'.

The only columns you will have available to use in the SELECT list are CL and HOLD