As we wait for a production release of MSSQL 2005, this is how I have done a lot of pivoting in SQL 2000.
A table like this:
needs to be pivoted like this:
which can be coded like this:
SUM(CASE WHEN Field = 'A' THEN Value ELSE 0) AS 'A',
SUM(CASE WHEN Field = 'B' THEN Value ELSE 0) AS 'B'
WHERE Field IN ('A', 'B')
GROUP BY Id
The caveat is, of course, that building the columns isn’t dynamic (like it is in Access) – you need to know what is in the column that needs to become the new column headers. This is good and bad. Your program wont get any unexpected results, but of course it’s a bit more complex. Its also the reason why I’ve included the WHERE clause which ensures that I’m not accidentally leaving out anything. On the other hand it is easy to create new column headers by changing the AS statements.