PIVOT in SQL Server 2005 and higher is a very useful and powerful tool in the world of business data analytics.
For some background information, please read Mark Wills's article Dynamic Pivot Procedure for SQL Server
As a quick review, the basic constraint of PIVOT in SQL Server is the requirement for hard coded values to be pivoted which becomes a huge issue when dealing with rolling periods like customer sales from 3 months ago as the pivoted values change over time.
Consequently, you can get around this constraint by utilizing dynamic SQL, which, as you have read previously, works very well for this application. Furthermore, with Mark's dynamic pivot procedure in your toolbox, you can apply this workaround to many different scenarios.
So why are we here?
Well, while working with business data requiring "rolling period" analysis, what if a view is required. Since stored procedures can't be executed from within a view, we, unfortunately, cannot implement Mark's approach.
As an alternative, there is a simple workaround that does not require the use of dynamic SQL which I will demonstrate in this article.
The Basic Principle
Reduce your data variation (in the column to be pivoted) to a list of generic, finite identifiers which describe the column's values rather than showing the actual data.
For rolling periods, this could simply be numbers like 1-12 signifying each month of the year. To explore the advantages of this alternative fully, we will use Mark's data for our example as well (thanks Mark).
So let's get started...
CREATE TABLE tst_CustSales (
TCS_ID INT Identity Primary Key Clustered,
TCS_Value MONEY )
-- now let's populate our tst_* tables
INSERT tst_CustSales (
SELECT * FROM (
SELECT 'Customer 1' as Customer,'20090101' as Date,
11 as Qty, 1001.00 as Val union all
SELECT 'Customer 1','20090201',12, 1002.00 union all
SELECT 'Customer 1','20090301',13, 1003.00 union all
SELECT 'Customer 1','20090401',14, 1004.00 union all
SELECT 'Customer 2','20090101',21, 2001.00 union all
SELECT 'Customer 2','20090201',22, 2002.00 union all
SELECT 'Customer 2','20090301',23, 2003.00 union all
SELECT 'Customer 2','20090401',24, 2004.00 union all
SELECT 'Customer 3','20090101',31, 3001.00 union all
SELECT 'Customer 4','20090201',42, 4002.00 union all
SELECT 'Customer 5','20090301',53, 5003.00 ) as src
For the particulars, please remember to read Dynamic Pivot Procedure for SQL Server
'tis that simple!
SELECT TCS_Customer AS Customer
, ISNULL(, 0.00) AS ThreeMonthsAgoValue
, ISNULL(, 0.00) AS TwoMonthsAgoValue
, ISNULL(, 0.00) AS OneMonthAgoValue
, ISNULL(, 0.00) AS CurrentMonthToDateValue
, DATEDIFF(MONTH, TCS_Date, GETDATE()) AS TCS_MonthsAgo
WHERE TCS_Date >=
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
FOR TCS_MonthsAgo IN (,,,)) pvt
The secret to our success is the use of SQL Date Functions exemplified by the following:
DATEDIFF(MONTH, TCS_Date, GETDATE())
Using DATEDIFF, we have converted our base data to the finite set of values we needed (in this case 0-3 representing a number of months ago from today). Now, we still have the limitation and tediousness of having to type out the hard coded values for the pivot; however, (,,,) will be valid whether we are in May or June or January.
As noted by Mark, you can use SELECT * for your column list. However, to control display order, you have to hard code the select list.
ISNULL(, 0.00) AS ThreeMonthsAgoValue
By using the explicit column list, it becomes advantageous as demonstrated above because it gives us the opportunity to use an alias that reflects a business-oriented value of the represented data, providing a more meaningful but static name for our periods.
(over dynamic SQL statements)
Simpler code maintenance.
Code can be used directly in views.
Reduced processing time and overhead (although probably not noticeable).
Hope you have found this tip handy and get good use out of it. I know I use it a great deal as we analyze a lot of data internally based on a rolling 12 months. Just don't lose sight of the fact that it shouldn't be the only PIVOT weapon in your arsenal as you will find tremendous need for the stored procedure (dynamic SQL) approach if you continue to do more reports such as these.
Kevin (aka MWVisa1)
Dynamic Pivot Procedure for SQL Server
SQL Date and Time Functions