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