21
Sep
09

PIVOT and UNPIVOT

I finally had the need to use PIVOT and UNPIVOT in SQL Server 2005 while creating a pretty large report for a client.
The report needed to show a number of groups and how much of their budget mapped to certain strategic objectives. This is a pretty standard crosstab report, but the problem is that the numbers don’t really exist anywhere – it’s all percentages of the aggregated sum of the amounts budgeted to different projects.
The previous iteration of the report just had hard-coded textboxes, mimicking a crosstab report but was really just a bunch of textboxes on the page. (The previous developer was under the gun for a proof of concept, and ended up winning us the contract to put this into production.)
The data looked something like this:

GroupName ProjectName BudgetedAmount FundedAmount FiscalYear
Group1 Project1 $500 $450 FY09
Group1 Project2 $300 $300 FY09
Group2 Project3 $1000 $950 FY09
Group3 Project4 $750 $600 FY09

I added a table like this:

FiscalYear GroupName StrategicObjective Percentage
FY09 Group1 Obj1 .50
FY09 Group1 Obj2 .50
FY09 Group2 Obj2 .75
FY09 Group2 Obj3 .25
FY09 Group3 Obj4 1.00

The query to apply the percentage and aggregate this data looks like this:

SELECT tt.GroupName, 
	tt.FiscalYear, 
	tf.StrategicObjective, 
	tf.Percentage * tt.FundedAmount AS Portion
FROM TestTable tt 
JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
AND tt.GroupName = tf.GroupName
WHERE tt.FiscalYear = 'FY09'

Producing:


Then I used the PIVOT query to produce the dataset I needed for my report:

SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
FROM ( 
	SELECT tt.GroupName, 
		tt.FiscalYear, 
		tf.StrategicObjective, 
		tf.Percentage * tt.FundedAmount AS Portion
	FROM TestTable tt 
	JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
	AND tt.GroupName = tf.GroupName
	WHERE tt.FiscalYear = 'FY09'
) p
PIVOT (
	SUM(Portion)
	FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS pvt

Producing:


If I need to produce another type of graphic showing the aggregate data again (bar charts or something mapped to the objectives) then I can do an UNPIVOT like this:

SELECT StrategicObjective, SUM(Amt) AS Amount
FROM (
	SELECT GroupName, [Obj1], [Obj2], [Obj3], [Obj4]
	FROM ( 
		SELECT tt.GroupName, 
			tt.FiscalYear, 
			tf.StrategicObjective, 
			tf.Percentage * tt.FundedAmount AS Portion
		FROM TestTable tt 
		JOIN TestFunding tf ON tt.FiscalYear = tf.FiscalYear
		AND tt.GroupName = tf.GroupName
		WHERE tt.FiscalYear = 'FY09'
		) p
	PIVOT (
		SUM(Portion)
		FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
	) AS pvt
) p2
UNPIVOT (
	Amt FOR StrategicObjective IN ([Obj1], [Obj2], [Obj3], [Obj4])
) AS unpvt
GROUP BY StrategicObjective

This produces the following resultset:


In conclusion, this could all be accomplished with CASE statements, but I think this is much cleaner and frankly easier to write and read. That hardcoded ‘FY09′ can just be replaced with a parameter in the report and we’re good to go for future years.


0 Responses to “PIVOT and UNPIVOT”


  1. No Comments