SQL 2000 Pivot / Crosstab

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:

Id Field Value
1 A 10
1 B 20
2 B 30
3 A 40

needs to be pivoted like this:

Id A B
1 10 20
2 0 30
3 40 0

which can be coded like this:

SELECT Id,
   SUM(CASE WHEN Field = 'A' THEN Value ELSE 0) AS 'A',
   SUM(CASE WHEN Field = 'B' THEN Value ELSE 0) AS 'B'
FROM MyTable
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.

Advertisements
Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s