CREATE TABLE Products (
product VARCHAR(30),
market_year INT,
value INT,
quantity INT,
CONSTRAINT pk_products
PRIMARY KEY (product, market_year));
INSERT INTO Products VALUES('Corn', 2003, 100, 20);
INSERT INTO Products VALUES('Corn', 2004, 200, 25);
INSERT INTO Products VALUES('Corn', 2005, 150, 30);
INSERT INTO Products VALUES('Corn', 2006, 150, 10);
product v2003 q2003 v2004 q2004 v2005 q2005 v2006 q2006
------- ----- ----- ----- ----- ----- ----- ----- -----
Corn 100 20 200 25 150 30 150 10
SELECT product,
SUM(CASE WHEN market_year = 2003 THEN value ELSE 0 END) AS v2003,
SUM(CASE WHEN market_year = 2003 THEN quantity ELSE 0 END) AS q2003,
SUM(CASE WHEN market_year = 2004 THEN value ELSE 0 END) AS v2004,
SUM(CASE WHEN market_year = 2004 THEN quantity ELSE 0 END) AS q2004,
SUM(CASE WHEN market_year = 2005 THEN value ELSE 0 END) AS v2005,
SUM(CASE WHEN market_year = 2005 THEN quantity ELSE 0 END) AS q2005,
SUM(CASE WHEN market_year = 2006 THEN value ELSE 0 END) AS v2006,
SUM(CASE WHEN market_year = 2006 THEN quantity ELSE 0 END) AS q2006
FROM Products
GROUP BY product;
SELECT product,
MAX([2003]) AS v2003,
MAX([3]) AS q2003,
MAX([2004]) AS v2004,
MAX([4]) AS q2004,
MAX([2005]) AS v2005,
MAX([5]) AS q2005,
MAX([2006]) AS v2006,
MAX([6]) AS q2006
FROM (SELECT product, market_year,
market_year - 2000 AS market_year2,
SUM(value) AS value,
SUM(quantity) AS quantity
FROM Products
GROUP BY product, market_year) AS T
PIVOT
(SUM(value) FOR market_year IN
([2003], [2004], [2005], [2006])) AS P1
PIVOT
(SUM(quantity) FOR market_year2 IN
([3], [4], [5], [6])) AS P2
GROUP BY product;
SELECT product,
[2003] / 1000 AS v2003,
[2003] % 1000 AS q2003,
[2004] / 1000 AS v2004,
[2004] % 1000 AS q2004,
[2005] / 1000 AS v2005,
[2005] % 1000 AS q2005,
[2006] / 1000 AS v2006,
[2006] % 1000 AS q2006
FROM (SELECT product, market_year,
value * 1000 + quantity AS value
FROM Products) AS T
PIVOT
(SUM(value) FOR market_year IN
([2003], [2004], [2005], [2006])) AS P;
Labels: pivot, t-sql programming