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)
FOR Field IN ([A], [B])
) AS PVT
ORDER BY ID
compared to the SQL 2000 version:
SUM(CASE WHEN Field = ‘A’ THEN Value ELSE 0 END) AS ‘A’,
SUM(CASE WHEN Field = ‘B’ THEN Value ELSE 0 END) AS ‘B’
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?