<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Pivot Rolling Periods Without Using Dynamic T-SQL

Published on
20,447 Points
8,047 Views
4 Endorsements
Last Modified:
Awarded
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_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
http://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
Comment
Author:Kevin Cross
0 Comments

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month