Stored Procedure - Pivot , table transformation Puzzle

Can someone give me some help on how to convert the Allocation table to the target table:

Allocation:

SA    Project         From            TO        Allocation      Comment
-------------------------------------------------------------------------------
Bin     p1          2008-11-01     2008-11-30       0.3            a
Bin     p1          2008-12-01     2008-12-31       0.3            a
Bin     p1          2009-01-01     2009-01-31       0.3            a
Bin     p1          2012-09-01     2012-09-30       0.5            b
Bin     p1          2012-10-01     2012-10-31       0.5            b
Bin     p1          2012-11-01     2012-11-30       0.5            b
Bin     p1          2012-12-01     2012-12-31       0.5            b
Mike    p2          2011-09-01     2011-09-30       0.3            c
Mike    p2          2011-10-01     2011-10-31       0.3            c
Mike    p2          2011-11-01     2011-11-30       0.3            c
Mike    p2          2011-12-01     2011-12-31       0.3            c
Mike    p2          2012-01-01     2012-01-31       0.3            c
Mike    p2          2012-05-01     2012-05-31       0.6            c
Mike    p2          2012-06-01     2012-06-30       0.6            c
Mike    p2          2012-07-01     2012-07-31       0.6            c
Mike    p1          2011-10-01     2011-10-31       0.45           d
Mike    p1          2011-11-01     2011-11-30       0.45           d
Mike    p1          2011-12-01     2011-12-31       0.45           d
Mike    p1          2012-10-01     2012-10-31       0.32           d
Mike    p1          2012-11-01     2012-11-30       0.32           d
Mike    p1          2012-12-01     2012-12-31       0.32           d

================================================================================

Target:

Please note that Nov Dec are in the previous year as Jan Feb ... Oct in.

SA  Project Year  Nov  Dec  Jan Feb Mar Apr May  Jun  Jul  Aug  Sep  Oct Comment Total  
---------------------------------------------------------------------------------------
Bin   p1    2008  0.3  0.3  0.3  0   0   0   0    0    0    0    0    0     a     0.9
Bin   p1    2011   0    0    0   0   0   0   0    0    0    0   0.5  0.5    b      1
Bin   p1    2012  0.5  0.5   0   0   0   0   0    0    0    0    0    0     b      1

Total             0.8  0.8  0.3  0   0   0   0    0    0    0   0.5  0.5          2.9

Mike  p1    2010   0    0    0   0   0   0   0    0    0    0    0   0.45   d     0.45
Mike  p1    2011  0.45 0.45  0   0   0   0   0    0    0    0    0   0.32   d     1.22
Mike  p1    2012  0.32 0.32  0   0   0   0   0    0    0    0    0    0     d     0.64
Mike  p2    2010   0    0    0   0   0   0   0    0    0    0   0.3  0.3    c     0.6
Mike  p2    2011  0.3  0.3  0.3  0   0   0  0.6  0.6  0.6   0    0    0     c     2.7

Total             1.07 1.07 0.3  0   0   0  0.6  0.6  0.6   0   0.3  1.07         5.61

Now I can get the Current table :

Current:

I can only make Jan Feb ... Dec in the same year. Have no clue how to make them in two consecutive years starting from Nov of last year.

SA  Project Year   Jan Feb Mar Apr  May  Jun  Jul   Aug  Sep   Oct    Nov  Dec  Comment
----------------------------------------------------------------------------------------
Bin   p1    2008    0   0   0   0    0    0    0     0    0     0    0.30  0.30   a
Bin   p1    2009  0.30  0   0   0    0    0    0     0    0     0      0    0     a
Bin   p1    2012    0   0   0   0    0    0    0     0   0.50  0.50  0.50  0.50   b
Mike  p1    2011    0   0   0   0    0    0    0     0    0    0.45  0.45  0.45   d
Mike  p1    2012    0   0   0   0    0    0    0     0    0    0.32  0.32  0.32   d
Mike  p2    2011    0   0   0   0    0    0    0     0   0.30  0.30  0.30  0.30   c
Mike  p2    2012  0.30  0   0   0  0.60  0.60  0.60  0    0     0     0     0     c

The key problem is how to tranform the Current table to Target table. In the Target table, for example 2008 Nov Dec and 2009 Jan Feb Mar Apr May Jun Jul Aug Sep Oct should be displayed in the same row. In addition, sum of each column and sum of each row shuld be added as well.

Any Suggestion is welcome.

It will be a lot appreciate if someone can help me to achieve this.

Thakns in advance.

Answers


Here is complete solution, you can check the code and check it on SQLFiddle Demo and I am going to put a brief explanation below the code.

SQLFiddle Demo

;WITH 
CTE_Pivot AS 
(
    SELECT 
         SA
        ,Project
        ,CASE WHEN MONTH([FROM]) <11 THEN YEAR([FROM])-1 ELSE YEAR([From]) END AS YEAR
        ,CASE WHEN MONTH([FROM]) = 11 THEN Allocation ELSE 0 END AS Nov
        ,CASE WHEN MONTH([FROM]) = 12 THEN Allocation ELSE 0 END AS [Dec]
        ,CASE WHEN MONTH([FROM]) = 1 THEN Allocation ELSE 0 END AS Jan
        ,CASE WHEN MONTH([FROM]) = 2 THEN Allocation ELSE 0 END AS Feb
        ,CASE WHEN MONTH([FROM]) = 3 THEN Allocation ELSE 0 END AS Mar
        ,CASE WHEN MONTH([FROM]) = 4 THEN Allocation ELSE 0 END AS Apr
        ,CASE WHEN MONTH([FROM]) = 5 THEN Allocation ELSE 0 END AS May
        ,CASE WHEN MONTH([FROM]) = 6 THEN Allocation ELSE 0 END AS Jun
        ,CASE WHEN MONTH([FROM]) = 7 THEN Allocation ELSE 0 END AS Jul
        ,CASE WHEN MONTH([FROM]) = 8 THEN Allocation ELSE 0 END AS Aug
        ,CASE WHEN MONTH([FROM]) = 9 THEN Allocation ELSE 0 END AS Sep
        ,CASE WHEN MONTH([FROM]) = 10 THEN Allocation ELSE 0 END AS Oct
        ,Allocation
        ,Comment
    FROM dbo.Allocation
)
,CTE_Rollup AS 
(
    SELECT SA, Project, YEAR 
        ,SUM(Nov) AS Nov
        ,SUM(Dec) AS Dec
        ,SUM(Jan) AS Jan
        ,SUM(Feb) AS Feb
        ,SUM(Mar) AS Mar
        ,SUM(Apr) AS Apr
        ,SUM(May) AS May
        ,SUM(Jun) AS Jun
        ,SUM(Jul) AS Jul
        ,SUM(Aug) AS Aug
        ,SUM(Sep) AS Sep
        ,SUM(Oct) AS Oct
        ,MAX(Comment) AS Comment
        ,SUM(Allocation) AS Total
    FROM CTE_Pivot 
    GROUP BY SA, Project, YEAR WITH ROLLUP
)
SELECT  
     CASE WHEN YEAR IS NULL THEN 'Total' ELSE SA END AS SA
    ,CASE WHEN YEAR IS NULL THEN NULL ELSE Project END AS Project
    ,YEAR 
    ,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct
    ,CASE WHEN YEAR IS NULL THEN NULL ELSE Comment END AS Comment
    ,Total 
FROM CTE_Rollup
WHERE SA IS NOT NULL AND (YEAR IS NOT NULL OR YEAR IS NULL AND Project IS NULL) 

First CTE is to pivot your data and create columns Nov, Dec etc. Data here is still not grouped. There is a separate row for each original row having values in only one month and zeros in other. Placing Jan-Oct months in next year is achieved using simple CASE statement (year+1 when month<11).

Second CTE is grouping data and using WITH ROLLUP hint to create subtotals. It returns almost what you want, with just a few extra rows (subtotals by each SA, Project combination, each SA and grand-totals).

Final query is just eliminating those rows and format few rows and columns to match your target table.

Note that I didn't have SQL Server 2005 at my hand to test it, but I am almost positive it should work fine

EDIT: SQLFiddle for updated version with blank rows before and after totals


Need Your Help

How to filter specific file with maven resource filtering

spring maven resources filtering

My spring+maven project has .properties and .xml resource files. I want to set up filtering for .properties only excluding all spring xml configs. This is pretty straightforward thing:

Choosing a test platform for .NET - MbUnit or the one by Microsoft?

.net unit-testing mstest mbunit

I have chosen these two as primary candidates. My thinking goes like this:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.