SQL Server 2005 PIVOT woes

I spent a morning playing with the new SQL Server 2005 PIVOT command in T-SQL.

I created a new database that has 2 columns: ID(int), Field(nvarchar(50), DataValue(nvarchar(50))

I put some data in:
insert into SomeData values('Shift', 'Day Shift')
insert into SomeData values('Shift', 'Night Shift')
insert into SomeData values('Shift', 'Day Shift')
insert into SomeData values('Shift', 'Morning Shift')

and then issued my PIVOT command:
SELECT ID, [Day Shift] AS DayShift, [Morning Shift] AS MorningShift, [Night Shift] AS NightShift FROM SOMEDATA
PIVOT
(
MIN([Field])
FOR DataValue IN ([Day Shift], [Morning Shift], [Night Shift])
) p

Things to watch out for...you must alias the PIVOT with a variable even if you don't use it anywhere - in my case I chose the letter p

The items in the FOR list cannot be calculated from a subquery so you must list them :(

If you import a SQL Server 2000 database that has the same schema as my example and then try to issue the above command, you'll get back a cryptic error like this:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '('.

You need to change the compatibility level of the database to SQL Server 2005 otherwise the functionality is restricted to SQL Server 2000 - PIVOT command is not available for SQL Server 2000. It took me a long time to figure this out because that error message is not much use! You change the compatibility level to SQL Server 2005 with sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'RevesbyProjectData', '90';

where the first parameter is the name of the database and the second parameter is the version of SQL Server: 80 is 2000 and 90 is 2005

Internet memory lane

Interesting trip down internet memory lane - Seems hard to believe in August 1995 there were only 18,957 web sites in the world.