SQL Pivot / Crosstab revisited

About 1½ years back I wrote a post about creating a pivot in SQL 2000. Now that SQL 2005 has been out for quite some time, I’ll follow up on that with the new PIVOT statement.

The same code in SQL 2005 would look like this:

SELECT Id, A = ISNULL([A],0), B = ISNULL([B],0)
FROM MyTable
PIVOT (
SUM(Value)           
FOR Field IN ([A], [B])       
) AS PVT
ORDER BY ID

compared to the SQL 2000 version:

SELECT Id,
SUM(CASE WHEN Field = ‘A’ THEN Value ELSE 0 END) AS ‘A’,   
SUM(CASE WHEN Field = ‘B’ THEN Value ELSE 0 END) AS ‘B’
FROM MyTable
WHERE Field IN (‘A’, ‘B’)
GROUP BY Id

(color coding is courtesy of SQL Server Management Studio – nice)

Note that, in order to avoid null-values, some extra ISNULL checking has to be added to the SQL 2005 version.

What I use this for is primarily a table that has about 3 years by 12000 entries by 1000 field-names, so what is really important – and this goes absolutely for the PIVOT statement as well – is to create a subselection of the field-names you want to use before pivoting. Performance is equally awful with the two solutions if you let SQL server plow through all the records before selecting the fields to pivot.

To me, it doesn’t seem to make much difference in the way the two versions work, and my informal performance measurements do not seem to indicate any difference. What could> have been done would be 1) to create the more dynamic method from Access, where you don’t have to specify which columns to extract, 2) making the optimizer (or whatever) implicitly create a subselection of fields, 3) build in the null-handling in PIVOT statement. But then again maybe I’m missing something here?

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