Re-summarizing data using UNPIVOT, ROW_NUMBER and CTEs


I had a question recently where a customer was presented a set of tables summarized by month and he was asked to re-summarize the data giving year to date summaries.

This was a “one time” request so we wanted to get results quickly and then refine the process once the customer decided how/if they wanted to report on the data more frequently. In my quest to eliminate cursors, loops and temp tables form my set-based life I wanted to present a solution that was contained in one SQL Query. As we will see below, there are some drawbacks to my approach, but I think this is a good illustration of using common table expressions, ranking functions and the UNPIVOT operator to solve a fairly common problem.

The structure of the source table was similar to:

CREATE TABLE dbo.MonthlyData (
    VendorId int
    , Jan int
    , Feb int
    , Mar int
    , Apr int
    , May int
    , Jun int
    , Jul int
    , Aug int
    , Sep int
    , Oct int
    , Nov int
    , Dec int
    CONSTRAINT pkc_MonthlyData PRIMARY KEY CLUSTERED (VendorId)
    );

Let’s populate some sample data:

SET NOCOUNT ON
DECLARE @i INT = 0
WHILE @i < 10000
BEGIN
    INSERT INTO dbo.MonthlyData VALUES (@i, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    SELECT @i = @i + 1    
    
END
GO

The first step is to break out the data by month and vendor. We can use the UNPIVOT operator to flip the month’s columns to rows.

SELECT *
  FROM (
SELECT VendorId
            , _01_Jan = Jan
            , _02_Feb = Feb
            , _03_Mar = Mar
            , _04_Apr = Apr
            , _05_May = May
            , _06_Jun = Jun
            , _07_Jul = Jul
            , _08_Aug = Aug
            , _09_Sep = Sep
            , _10_Oct = Oct
            , _11_Nov = Nov
            , _12_Dec = Dec
    FROM dbo.MonthlyData
    ) AS p
UNPIVOT
    (Amount FOR MonthAbbr IN 
        (_01_Jan, _02_Feb, _03_Mar, _04_Apr, _05_May, _06_Jun
         , _07_Jul, _08_Aug, _09_Sep, _10_Oct, _11_Nov, _12_Dec
         ) 
    ) a
        

We have prefixed each month name with a sortable number so that we can order the results when we calculate our totals. The result of the above looks like:

VendorId Amount MonthAbbr
1 1 _01_Jan
2 1 _02_Feb
3 1 _03_Mar
4 1 _04_Apr

We can now use this query as the input to a common table expression:

WITH unpivoted_cte as (
    SELECT VendorId
            , MonthAbbr
            , Amount 
            , MonthId = row_number() over(partition by VendorId order by MonthAbbr)
    FROM 
        (SELECT VendorId
                , _01_Jan = Jan
                , _02_Feb = Feb
                , _03_Mar = Mar
                , _04_Apr = Apr
                , _05_May = May
                , _06_Jun = Jun
                , _07_Jul = Jul
                , _08_Aug = Aug
                , _09_Sep = Sep
                , _10_Oct = Oct
                , _11_Nov = Nov
                , _12_Dec = Dec
        FROM dbo.MonthlyData) AS p
    UNPIVOT
        (Amount FOR MonthAbbr IN 
            (_01_Jan, _02_Feb, _03_Mar, _04_Apr, _05_May, _06_Jun
             , _07_Jul, _08_Aug, _09_Sep, _10_Oct, _11_Nov, _12_Dec
         )
    )AS unpvt
    )
SELECT base.MonthId
        , base.VendorId
        , base.Amount
  FROM unpivoted_cte base

The MonthAbbr is converted to an ID of 1-12.

Now we can calculate a year to date and a rolling three month calculation for a given vendor…

 

WITH unpivoted_cte as (
    SELECT VendorId
            , MonthAbbr
            , Amount 
            , MonthId = row_number() over(partition by VendorId order by MonthAbbr)
    FROM 
        (SELECT VendorId
                , _01_Jan = Jan
                , _02_Feb = Feb
                , _03_Mar = Mar
                , _04_Apr = Apr
                , _05_May = May
                , _06_Jun = Jun
                , _07_Jul = Jul
                , _08_Aug = Aug
                , _09_Sep = Sep
                , _10_Oct = Oct
                , _11_Nov = Nov
                , _12_Dec = Dec
        FROM dbo.MonthlyData) AS p
    UNPIVOT
        (Amount FOR MonthAbbr IN 
            (_01_Jan, _02_Feb, _03_Mar, _04_Apr, _05_May, _06_Jun
             , _07_Jul, _08_Aug, _09_Sep, _10_Oct, _11_Nov, _12_Dec
         )
    )AS unpvt
    )
SELECT base.MonthId
        , base.VendorId
        , base.Amount
        , YTD = ytd.Amount
        , Rolling3 = rolling3.Amount
  FROM unpivoted_cte base
  LEFT JOIN (
        SELECT base.VendorId
                , base.MonthId 
                , Amount = sum(compare.amount)
          FROM unpivoted_cte base
          JOIN unpivoted_cte compare
            ON base.vendorId = compare.vendorId
           AND base.monthId >=     compare.monthId
         GROUP BY base.VendorId
                    , base.MonthId
        ) ytd
    ON base.VendorId = ytd.VendorId
   AND base.MonthId = ytd.MonthId
  LEFT JOIN (
        SELECT base.VendorId
                , base.MonthId 
                , Amount = sum(compare.amount)
          FROM unpivoted_cte base
          JOIN unpivoted_cte compare
            ON base.vendorId = compare.vendorId
           AND compare.monthId between base.MonthId - 2 and base.MonthId
         GROUP BY base.VendorId
                    , base.MonthId
        ) rolling3
    ON base.VendorId = rolling3.VendorId
   AND base.MonthId = rolling3.MonthId
 WHERE base.VendorId = 999
 ORDER BY base.VendorId, base.MonthId

GO

Here we add two left joins that each access the CTE. In the case of YTD we have:

 LEFT JOIN (
        SELECT base.VendorId
                , base.MonthId 
                , Amount = sum(compare.amount)
          FROM unpivoted_cte base
          JOIN unpivoted_cte compare
            ON base.vendorId = compare.vendorId
           AND base.monthId >=     compare.monthId
         GROUP BY base.VendorId
                    , base.MonthId
        ) ytd
    ON base.VendorId = ytd.VendorId
   AND base.MonthId = ytd.MonthId

We use the CTE as a base and join the CTE to itself where the “compare” month is less than the base month. So, for MondId 4 in the “base” we will return months 1, 2, 3 and 4. We then sum the amounts grouping by vendor and base month.

The result of this query is:

MonthId VendorId Amount YTD Rolling3
1 999 1 1 1
2 999 1 2 2
3 999 1 3 3
4 999 1 4 3

There are a couple of open issues with this approach:

  • We are assuming that there is only one per vendor. If we have more than one year we would need to modify the unpivot and use the year in the rolling 3 month and the YTD calculations.
  • This is great for a one time re-summarization; however, there are other approaches including using SSIS to unpivot if you are loading data from a flat file and breaking the query into multiple steps and populating a work table that holds the unpivoted data.

In my opinion, if you find yourself performing these types of queries in the context of an application or reporting system and not just for one time data analysis then you should look closely at your data structures and update your schema to give you access to the data in a more usable form. That said, this may be a good stop gap as you are re-architecting an existing solution or you have encountered a solution where you cannot change these data structures.


Comments (1)
  1. Anonymous says:

    Awesome– This is useful in every financial and accounting applicaiton

Comments are closed.

Skip to main content