pivot / unpivot query

http://it.toolbox.com/blogs/db2luw/pivot-query-12757

http://it.toolbox.com/blogs/db2luw/unpivot-query-12798

CREATE TABLE Sales (Year INT, Quarter INT, Results INT)

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

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

 
YEAR Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- ----------- 
2004 20 30 15 10 
2005 18 40 12 27
 
 
CREATE TABLE SalesAgg
( year INTEGER,
q1 INTEGER,
q2 INTEGER,
q3 INTEGER,
q4 INTEGER );
 
YEAR Q1 Q2 Q3 Q4 
----------- ----------- ----------- ----------- ----------- 
2004 20 30 15 10 
2005 18 40 12 27
 
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);
 
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