product_nbr qtr1 sales_qtr1 qtr2 sales_qtr2 qtr3 sales_qtr3 qtr4 sales_qtr4
----------- ------ ----------- ------ ----------- ------ ----------- ------ -----------
1 2008Q1 100 2008Q2 20 2008Q3 15 2008Q4 10
2 2008Q1 80 2008Q2 15 2008Q3 20 2008Q4 10
3 2008Q1 70 2008Q2 5 2008Q3 10 2008Q4 15
product_nbr qtr sales
----------- ------ -----------
1 2008Q1 100
1 2008Q2 20
1 2008Q3 15
1 2008Q4 10
2 2008Q1 80
2 2008Q2 15
2 2008Q3 20
2 2008Q4 10
3 2008Q1 70
3 2008Q2 5
3 2008Q3 10
3 2008Q4 15
SELECT product_nbr,
CASE n
WHEN 1 THEN qtr1
WHEN 2 THEN qtr2
WHEN 3 THEN qtr3
WHEN 4 THEN qtr4
END AS qtr,
CASE n
WHEN 1 THEN sales_qtr1
WHEN 2 THEN sales_qtr2
WHEN 3 THEN sales_qtr3
WHEN 4 THEN sales_qtr4
END AS sales
FROM QuarterlySales AS S
CROSS JOIN
(SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4) AS Nums(n);
SELECT product_nbr, qtr, sales
FROM
(SELECT product_nbr,
qtr1, sales_qtr1,
qtr2, sales_qtr2,
qtr3, sales_qtr3,
qtr4, sales_qtr4
FROM QuarterlySales) AS S
UNPIVOT
(qtr FOR qtrx IN (qtr1, qtr2, qtr3, qtr4)) AS U1
UNPIVOT
(sales FOR sales_qtrx IN (sales_qtr1, sales_qtr2,
sales_qtr3, sales_qtr4)) AS U2
WHERE RIGHT(sales_qtrx, 1) = RIGHT(qtrx, 1);
Labels: t-sql programming, unpivot