Pivoting
The first will convert rows into columns.
Let's assume that we have the following table definition
CREATE TABLE Sales (Year INT, Quarter INT, Results INT)
Which contains the following data
YEAR QUARTER RESULTS
----------- ----------- -----------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27
What I want is a query that shows one row per year with each column being the results of the sales by quarter (i.e. one column per quarter). This is the result I want to see
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30 15 10
2005 18 40 12 27
Here is the query that gives this to you.
SELECT Year,
MAX(CASE WHEN Quarter = 1
THEN Results END) AS Q1,
MAX(CASE WHEN Quarter = 2
THEN Results END) AS Q2,
MAX(CASE WHEN Quarter = 3
THEN Results END) AS Q3,
MAX(CASE WHEN Quarter = 4
THEN Results END) AS Q4
FROM Sales
GROUP BY Year
In this query we scan the SALES table and return the Year followed by 4 other columns. If we ignore the GROUP BY for one moment, the value of the 4 columns will be either the value in the RESULTS column if the Quarter is equal to the Quarter in the CASE expression or it will be NULL if it doesn't match. Here is what the results would look like if there was no GROUP BY clause (and no MAX function).
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 - - -
2004 - 30 - -
2004 - - 15 -
2004 - - - 10
2005 18 - - -
2005 - 40 - -
2005 - - 12 -
2005 - - - 27
Then we use the MAX function and group by YEAR. This results in one row per year with the RESULTS in each of the 4 quarter columns. The MAX function is used here as it works with all compatible data types including strings.
Unpivoting
CREATE TABLE SalesAgg
( year INTEGER,
q1 INTEGER,
q2 INTEGER,
q3 INTEGER,
q4 INTEGER );
With the following data in it:
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30 15 10
2005 18 40 12 27
But what I want is to produce one row for each Year/Quarter combination with the 3rd column being the results that appear in the year/quarter cell above. Here are the results I'm looking for:
YEAR QUARTER RESULTS
----------- ----------- -----------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27
Here is the query that will produce these results very efficiently:
SELECT S.Year, Q.Quarter, Q.Results
FROM SalesAgg AS S,
TABLE (VALUES(1, S.q1),
(2, S.q2),
(3, S.q3),
(4, S.q4))
AS Q(Quarter, Results);
What is this query doing? The first thing it does is pull out the Year from the SALESAGG table (S.YEAR) then we pull out the Q.Quarter and Q.Results columns. But what is this Q table? This refers to the table function listed in the FROM clause. This table function uses that multi line VALUES clause that I referred to in my previous posting which creates a table out of nowhere. But in this case we are actually using a correlated query. Within the values clause you can see a reference to the columns of S (S.q1, S.q2, S.q3, S.q4).
What is happening here is that for every row we pull out of SalesAgg we get the year value and then combine that with the quarter number (1,2,3,4 which is the first value in the VALUES clause) and then the results which is the value of S.Q1, S.Q2, S.Q3 and S.Q4. But instead of showing then as separate columns this VALUES clause shows them as 4 separate rows. And all with only one pass of the data.
Author: Chris Eaton (Product Manager, IBM)
