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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
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
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?

T-SQL
1
SELECT * FROM YearlyProfit
SELECT * FROM YearlyProfit

Year Q1 Q2 Q3 Q4
2008 400.00 300.20 500.50 400.00
2009 499.50 100.20 555.55 500.00
2010 466.20 300.40 544.44 499.00
2011 433.60 222.55 533.33 522.00

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)

T-SQL
1
2
3
4
5
6
7
8
9
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
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

Year QuarterlyProfit Quarter
2008 400.00 Q1
2008 300.20 Q2
2008 500.50 Q3
2008 400.00 Q4
2009 499.50 Q1
2009 100.20 Q2
2009 555.55 Q3
2009 500.00 Q4
2010 466.20 Q1
2010 300.40 Q2
2010 544.44 Q3
2010 499.00 Q4
2011 433.60 Q1
2011 222.55 Q2
2011 533.33 Q3
2011 522.00 Q4

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

T-SQL
1
2
3
4
5
6
7
8
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)
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