Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Pivot Rolling Periods Without Using Dynamic T-SQL

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
Published:
Updated:
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_Date DATETIME,
                         TCS_Quantity INT,
                         TCS_Value MONEY )
                      ;
                      
                      -- now let's populate our tst_* tables
                       
                      INSERT tst_CustSales (
                         TCS_Customer, 
                         TCS_Date, 
                         TCS_Quantity, 
                         TCS_Value
                      ) 
                      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
                      ;

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
                      FROM (
                         SELECT TCS_Customer
                         , TCS_Value
                         , DATEDIFF(MONTH, TCS_Date, GETDATE()) AS TCS_MonthsAgo 
                         FROM tst_CustSales
                         WHERE TCS_Date >= 
                            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
                      ) b
                      PIVOT (SUM(TCS_Value) 
                         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:

DATEDIFF(MONTH, TCS_Date, GETDATE())

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)


References:

Dynamic Pivot Procedure for SQL Server
https://www.experts-exchange.com/A_653.html

Using PIVOT
http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx 

SQL Date and Time Functions
http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx
4
10,348 Views
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.