SQL Puzzle | Multiple Column Pivoting Puzzle

Puzzle Statement

Recently I was working on an assignment and landed with this puzzle. Here you have to Pivot data for according to multiple columns.

Please check out the sample input values and expected output below.

Sample Input

Area BillYear Month Cost1 Cost2 Cost3
Ground Campus 2015 April -0.080 0.000 0.000
Ground Campus 2014 April -0.050 0.000 0.000
Ground Campus 2015 April -0.060 0.000 0.000
Ground Campus 2014 April -0.030 0.000 0.000
Ground Campus 2015 April -0.020 0.000 0.000
Ground Campus 2014 April -0.160 0.000 0.000
Ground Campus 2015 April -0.190 0.000 0.000
Main Campus 2014 April 779441.480 13803609.000 0.056
Main Campus 2015 April 740261.410 13939101.000 0.053
Main Campus 2014 April 163702.500 195366.000 0.838
Main Campus 2015 April 172945.140 208679.000 0.829
Main Campus 2014 April 31468.470 0.000 0.000
Main Campus 2015 April 60485.870 0.000 0.000
Main Campus 2014 April 65155.690 24964.000 2.610
Main Campus 2015 April 79399.980 30452.000 2.607
Office 2014 April 21758.040 305491.000 0.071
Office 2015 April 19442.510 276304.000 0.070
Office 2014 April 1044.840 0.000 0.000
Office 2015 April 975.350 0.000 0.000
Office 2014 April 915.820 341.000 2.686
Office 2015 April 859.020 318.000 2.701

Expected Output

Area Month C1_2014 C1_2015 C2_2014 C2_2015 C3_2014 C3_2015
Ground Campus April -0.240 -0.350 0.000 0.000 0.000 0.000
Main Campus April 1039768.140 1053092.400 14023939.000 14178232.000 3.504 3.489
Office April 23718.700 21276.880 305832.000 276622.000 2.757 2.771

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE MultiColumnPivoting ( Area VARCHAR(100) ,BillYear INT ,Month VARCHAR(15) ,Cost1 DECIMAL(15,3) ,Cost2 DECIMAL(15,3) ,Cost3 DECIMAL(15,3) ) GO INSERT INTO MultiColumnPivoting VALUES ('Ground Campus', 2015, 'April' ,-0.08, 0, 0 ), ('Ground Campus', 2014, 'April' ,-0.05, 0, 0), ('Ground Campus', 2015, 'April' ,-0.06, 0, 0), ('Ground Campus', 2014, 'April' ,-0.03, 0, 0), ('Ground Campus', 2015, 'April' ,-0.02, 0, 0), ('Ground Campus', 2014, 'April' ,-0.16, 0, 0), ('Ground Campus', 2015, 'April' ,-0.19, 0, 0), ('Main Campus', 2014, 'April' ,779441.48, 13803609 ,0.056), ('Main Campus', 2015, 'April' ,740261.41, 13939101, 0.053), ('Main Campus', 2014, 'April' ,163702.5, 195366 ,0.838), ('Main Campus', 2015, 'April' ,172945.14, 208679 ,0.829), ('Main Campus', 2014, 'April' ,31468.47, 0 ,0), ('Main Campus', 2015, 'April' ,60485.87, 0 ,0), ('Main Campus', 2014, 'April' ,65155.69, 24964, 2.61), ('Main Campus', 2015, 'April' ,79399.98, 30452 ,2.607), ('Office', 2014, 'April' ,21758.04 ,305491, 0.071), ('Office', 2015, 'April' ,19442.51 ,276304, 0.07), ('Office', 2014, 'April' ,1044.84 ,0, 0), ('Office', 2015, 'April' ,975.35 ,0 ,0), ('Office', 2014, 'April' ,915.82 ,341, 2.686), ('Office', 2015, 'April' ,859.02 ,318, 2.701) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
 ```-- ;WITH CTE AS ( SELECT Area ,BillYear ,Month ,Cost1 ,Cost2 ,Cost3 ,CONCAT('C1_' , BillYear) C1 ,CONCAT('C2_' , BillYear) C2 ,CONCAT('C3_' , BillYear) C3 FROM MultiColumnPivoting ) ,CTE1 AS ( SELECT * FROM CTE PIVOT (SUM(Cost1) FOR C1 IN ( [C1_2014],[C1_2015])) p1 PIVOT (SUM(Cost2) FOR C2 IN ( [C2_2014],[C2_2015])) p2 PIVOT (SUM(Cost3) FOR C3 IN ( [C3_2014],[C3_2015])) p3 ) SELECT Area,Month,SUM([C1_2014]) [C1_2014] ,SUM([C1_2015]) [C1_2015],SUM([C2_2014]) [C2_2014],SUM([C2_2015]) [C2_2015],SUM([C3_2014]) [C3_2014],SUM([C3_2015]) [C3_2015] FROM CTE1 GROUP BY Area,Month ORDER BY Area,Month -- ```