Pivots Turn Ugly to Pretty
Here's a cool way to look at data, formatted using simple T-SQL PIVOT syntax.
- By Eric Johnson
You will often find that data stored in rows in normalized tables isn't in the prettiest form to display to an end user. Often, data will make more of an impact if it is displayed as a single row for each object with multiple columns of data. If you are looking at sales for specific customers, it is more pleasing to see one row for each customer and a column for each year of data. It also makes it easier to compare one client to another for a particular year.
Getting the data in a nice format means turning to some PIVOT syntax. Let's look at an example of using PIVOT to get data out rows and into columns. Take a look at the following sales table (some rows omitted):
SELECT * FROM DBO.SALES
customerid year sales Qtr
----------- ----------- --------------------- -----------
1 2007 25000.00 1
1 2008 22000.00 1
1 2007 34555.00 2
2 2007 35500.00 1
2 2008 56800.00 1
1 2007 784747.00 3
3 2007 15600.00 1
3 2008 95600.00 1
... ... ... ...
This table contain one row of sales data for each quarter for each customer. This is a little hard to look through and we only have three customers in this sample table. We can use the PIVOT syntax to make this data more clear. First I will show you the code and then we will look at how it works. Running the following SELECT with a PIVOT returns the data shown:
SELECT * FROM
(SELECT customerid, year, sales from dbo.SALES) AS SourceTable
FOR year IN (, )
) AS PivotTable
customerid 2007 2008
----------- --------------------- ---------------------
1 867746.00 52348687.00
2 483332975.00 35290565548.00
3 85647195.00 840055799.00
This query provides a nice, clear table with one row for each client and a column with the sales total for that year.
Let's look at the syntax and see how this is defined. The meat of this query is the two derived tables; in this example they are called SourceTable and PivotTable. The first SELECT defines your raw data that will be fed into the PIVOT. This is just the SELECT statement we looked at earlier returning the columns we want to see. The source data is followed by the PIVOT portion of the code. Here, we define that we want to see a SUM of the sales data for the year column where the year value is 2007 or 2008. The result is the table shown above.
By no means is this the only way to pivot data -- you'll find other methods in SSIS, third-party applications and even T-SQL itself. The example I show here happens to be a handy little syntax and since you may encounter code or situations that call for it, it is good to know how PIVOT works. You can even build more complex pivots using the PIVOT operator. So go forth and play with this a little; happy coding!
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.