Pivot Table
You hear the term pivot table frequently when you use Excel or do a lot of data mining. Most reporting tools have a pivot table report type built in. Did you know there really is a device called a pivot table? Back in the old days, this was how we would turn rows of data into columns of data (pivot).
Today I’m going to give you a simple example of a pivot table with a practical application you can use. Frequently a report is necessary to get the sales for a year and break it out by quarter. Often those sales are presented with some differentiating dimension such as sale person, region or the like. For each instance of the dimension, the report then demonstrates the sales for four quarters.
Using a pivot table this is a simple query. I’m going to demonstrate the pivot table without the third dimension.
;with quarters (startDTM, endDTM, q1, q2, q3,q4)
AS
(
SELECT ‘1/1/2013’, ‘3/31/2013 23:59:59:997’, 1, 0, 0, 0
UNION ALL SELECT ‘4/1/2013’, ‘6/30/2013 23:59:59:997’, 0, 1, 0, 0
UNION ALL SELECT ‘7/1/2013’, ‘9/30/2013 23:59:59:997’, 0, 0, 1, 0
UNION ALL SELECT ’10/1/2013′, ’12/31/2013 23:59:59:997′, 0, 0, 0, 1
)
SELECT
ISNULL(SUM(Dales.GrossSales * Quarters.q1) , 0) AS Q1Sales
, ISNULL(SUM(Dales.GrossSales * Quarters.q2) , 0) AS Q2Sales
, ISNULL(SUM(Dales.GrossSales * Quarters.q3) , 0) AS Q3Sales
, ISNULL(SUM(Dales.GrossSales * Quarters.q4), 0) AS Q4Sales
from Quarters`
LEFT OUTER JOIN Sales ON Sales.SaleDate BETWEEN Quarters.startDTM and Quarters.endDTM
The CTE (Common Table Expression) quarters can be replaced with a permanent table or some other technique. For me, it operates as both a filter (only joins where rows fall between the dates provided) and a pivot, placing each sale in the appropriate quarter category.
Notice I perform a LEFT OUTER JOIN to the sales data because there may not yet be any sales for a given quarter. The ISNULL function returns zero instead of null for those quarters not having any sales activity.
You can take this query and join it to other tables to return other dimensions. Include those dimensions in a group by clause and you have a fully functional pivot query.
SQL Server supports a built in pivot query syntax. You can use it without having to create or maintain your own pivot table. I like this technique for those queries I have to run a lot. It can perform much faster, especially with a permanent table.
So, now you know a little more about an original pivot table and pivot query. It is a matrix operating as a mask, when used in conjunction with an aggregate function such as Sum, Min, Max, categorizing elements returned on a row by row basis into individual columns.
One final interesting thing about using this pivot table method is that you can perform multiple aggregate functions, since you define your own columns. You can, in a single query, get sum, min, max, stdev, etc. I have even written my own CLR function to get median which works well with this kind of query.
Are you 64 bit Yet? – Reader Responses…
Fareed:
I would like to mention that 64bit is not always better than 32bit, 64bit application usually use and consume more memory and storage, I rather avoid 64bit version when you don’t need it, (the main advantage of x64 for large direct memory address and larger files to handle)
for .net developers they can target their software to Any CPU, so it will depend on the .net framework installed or used..
As always, feel free to leave your comments below, or drop me an Email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting Upgrading to SQL Server 2012
In this article, Alexander Chigrik explains some problems that you can have when you upgrade to SQL Server 2012.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008/2012
Written by Townsend Security
Simplify encryption and key management on … (read more)