sales_year first_quarter second_quarter third_quarter fourth_quarter
---------- ------------- -------------- ------------- --------------
2006 211203.50 381594.95 439187.00 503155.80
2007 231205.10 451101.25 601209.40 531907.30
sales_year sales_quarter sales_amount
----------- ------------- ------------
2006 1 211203.50
2006 2 381594.95
2006 3 439187.00
2006 4 503155.80
2007 1 231205.10
2007 2 451101.25
2007 3 601209.40
2007 4 531907.30
-- Create sample table
CREATE TABLE QuarterlySales (
sales_year INT PRIMARY KEY,
first_quarter DECIMAL(10, 2),
second_quarter DECIMAL(10, 2),
third_quarter DECIMAL(10, 2),
fourth_quarter DECIMAL(10, 2));
-- Insert data
INSERT INTO QuarterlySales VALUES(2006, 211203.50, 381594.95, 439187.00, 503155.80);
INSERT INTO QuarterlySales VALUES(2007, 231205.10, 451101.25, 601209.40, 531907.30);
-- Method 1) Using UNION
SELECT sales_year,
1 AS sales_quarter,
first_quarter AS sales_amount
FROM QuarterlySales
UNION ALL
SELECT sales_year, 2, second_quarter
FROM QuarterlySales
UNION ALL
SELECT sales_year, 3, third_quarter
FROM QuarterlySales
UNION ALL
SELECT sales_year, 4, fourth_quarter
FROM QuarterlySales
ORDER BY sales_year, sales_quarter;
-- Method 2) Using cross join with table with numbers
SELECT sales_year,
qtr AS sales_quarter,
CASE qtr
WHEN 1 THEN first_quarter
WHEN 2 THEN second_quarter
WHEN 3 THEN third_quarter
WHEN 4 THEN fourth_quarter
END AS sales_amount
FROM QuarterlySales
CROSS JOIN
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4) AS Quarters(qtr)
ORDER BY sales_year, sales_quarter;
-- Method 3) Using the UNPIVOT operator in SQL Server 2005
SELECT sales_year,
CASE sales_quarter
WHEN 'first_quarter' THEN 1
WHEN 'second_quarter' THEN 2
WHEN 'third_quarter' THEN 3
WHEN 'fourth_quarter' THEN 4
END AS sales_quarter,
sales_amount
FROM QuarterlySales
UNPIVOT
(sales_amount FOR
sales_quarter IN
(first_quarter, second_quarter,
third_quarter, fourth_quarter)) AS U
ORDER BY sales_year, sales_quarter;
Labels: pivot, t-sql programming, unpivot