There's two interesting posts over at the MSDN blogs from Craig Freedman about the new PIVOT operator in SQL 2005.  First, he gives a nice overview of the operator and how to use it, and then he follows it up with a performance analysis as well.  A good read, check it out. 

I hope to cover both the PIVOT and also the UNPIVOT operators a little myself at some point in a future post.

see also:

• Keep those SQL Crosstabs flexible!
• .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable
• .NET CrossTabs versus SQL Server CrossTabs
• ASP and ADO Pivots -- Old School CrossTabs
• Sorting Columns with the C# Pivot Function