Pivot Rolling Periods Without Using Dynamic T-SQL: SQL, SQL 2005, SQL 2008, PIVOT, T-SQL, crosstab, horizontal data, date functions

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
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

Select all 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
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Select all 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())

1:

Select all 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

1:

Select all 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

Pivot Rolling Periods Without Using Dynamic T-SQL

SQL

SQL 2005

SQL 2008

PIVOT

T-SQL

crosstab

horizontal data

date functions

MS SQL Server

About The Author

More Articles From mwvisa1

Comments

Add your Comment

Top MS SQL Server Experts