Pivot Rolling Periods Without Using Dynamic T-SQL

Published on
20,331 Points
4 Endorsements
Last Modified:
Community Pick
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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...

Mark's Data
CREATE TABLE tst_CustSales (
   TCS_ID INT Identity Primary Key Clustered,
   TCS_Customer varchar(60),
   TCS_Quantity INT,
   TCS_Value MONEY )

-- now let's populate our tst_* tables
INSERT tst_CustSales (
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

Open in new window

For the particulars, please remember to read Dynamic Pivot Procedure for SQL Server.

The Code
SELECT TCS_Customer AS Customer
, ISNULL([3], 0.00) AS ThreeMonthsAgoValue
, ISNULL([2], 0.00) AS TwoMonthsAgoValue
, ISNULL([1], 0.00) AS OneMonthAgoValue
, ISNULL([0], 0.00) AS CurrentMonthToDateValue
   SELECT TCS_Customer
   , TCS_Value
   FROM tst_CustSales
   WHERE TCS_Date >= 
) b
   FOR TCS_MonthsAgo IN ([3],[2],[1],[0])) pvt

Open in new window

'tis that simple!  

The Methodology
The secret to our success is the use of SQL Date Functions exemplified by the following:


Open in new window

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, ([3],[2],[1],[0]) 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([3], 0.00) AS ThreeMonthsAgoValue

Open in new window

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.

The Advantages (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.

Happy coding!

Best regards,

Kevin (aka MWVisa1)


Dynamic Pivot Procedure for SQL Server


SQL Date and Time Functions
Author:Kevin Cross

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month