Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Pivot

--

;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

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com