Where Should I Pivot?
Recently I was talking with a young programmer just graduated from College about pivoting data. He had been studying the Pivot capabilities built into SQL Server. However, he had also seen the same capability when producing pivoted reports in college using Crystal Reports and Excel. So, his natural question was, which way should I pivot data? Is there just one solution, or do I use different tools? If different tools, how do I go about making a decision?
I first started pivoting data using Microsoft SQL Server 4.21. I had read a section in the SQL Server Administration Guide demonstrating how to create a “Pivot Table” and then how to write a pivot query using the table. I have an article here on SSWUG from a few years back demonstrating Pivot tables if you are interested.
Another way to pivot data is to use a Case statement, or even (believe it or not) CHARINDEX. SQL Server 2005 released the first native syntax in SQL Server to produce a pivot query without requiring a real pivot table for performance.
I used pivot reports in Excel, Power Builder, Crystal Reports and even Microsoft Access. All of these different tools have features and capabilities where they stand apart.
So, if you were to answer my college friend, what would your advice be? Pivot in the query tool, or pivot in the database? Maybe pivot in either one depending on the situation. If situational, what are the factors used for making your decision?
Share your advice with us by writing in to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
search syscomments for ‘word’ references in objects
A quick & ‘dirty’ way of searching through syscomments for a ‘word’, returning the name and type of objects where the word … (read more)