In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In yesterday’s post I covered the PIVOT operator, in today’s post I will cover the UNPIVOT operator. UNPIVOT is the reverse of PIVOT, now instead of going from a narrow table to a wide resultset, you will go from a wide table to a narrow resultset. Today’s post will be much shorter than the PIVOT post, I also think that PIVOT is used a lot more than UNPIVOT.

Let’s see how this works, first create this table

CREATE TABLE YearlyProfit (Year int, 
					Q1 decimal(10,2),
					Q2 decimal(10,2),
					Q3 decimal(10,2),
					Q4 decimal(10,2));
GO
INSERT INTO YearlyProfit VALUES (2008,400,300.2,500.5,400);
INSERT INTO YearlyProfit VALUES (2009,499.5,100.2,555.55,500);
INSERT INTO YearlyProfit VALUES (2010,466.2,300.4,544.44,499);
INSERT INTO YearlyProfit VALUES (2011,433.6,222.55,533.33,522);

GO

A simple select will reveal that we have a column for each quarter….can you say denormalized?

SELECT * FROM YearlyProfit

The UNPIVOT query is very simple. The Quarter column will hold the names of the 4 quarter columns(Q1, Q2, Q3, Q4) as values, QuarterlyProfit will have the numeric values of the 4 quarter columns(Q1, Q2, Q3, Q4)

SELECT year,QuarterlyProfit,Quarter
FROM 
   (SELECT year,Q1, Q2, Q3, Q4
	FROM YearlyProfit) y
UNPIVOT
   (QuarterlyProfit FOR Quarter IN 
      (Q1, Q2, Q3, Q4)
)AS unpvt;
GO

And as you can see, our output now has all the quarters in one column

Now compare that to this query, the output is the same but we are not using UNPIVOT but a CROSS JOIN combined with a CASE statement

SELECT year,CASE Quarter 
	WHEN 'Q1' THEN Q1 
	WHEN 'Q2' THEN Q2
	WHEN 'Q3' THEN Q3
	WHEN 'Q4' THEN Q4 end as QuarterlyProfit,
	Quarter
	FROM YearlyProfit
CROSS JOIN (VALUES('Q1'),('Q2'),('Q3'),('Q4')) as Years(Quarter)

That looks much more complicated than the UNPIVOT query, don’t you think?

That is all for UNPIVOT….come back tomorrow for day 9 of this series