Pivoting on Multiple Columns
Pivoting data on more than one column is not a common request. But there are times when it is a very useful technique for reporting purposes. Here is one example to demonstrate different methods to handle that.
The goal is to report product data by pivoting columns with value and quantity for each product based on year. Here is the script to create the table and insert sample data.
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);
The request is to produce the following output:
product v2003 q2003 v2004 q2004 v2005 q2005 v2006 q2006
------- ----- ----- ----- ----- ----- ----- ----- -----
Corn 100 20 200 25 150 30 150 10
As usual, the first suspect for pivoting solution is the CASE function. Using CASE expressions is the most flexible method to manipulate data for pivoting. There is not much difference when pivoting a single or multiple columns. Here is the solution with CASE:
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;
Next, let's look at the PIVOT operator that was added in SQL Server 2005. The PIVOT operator has a few limitations, and one of them is that it supports pivoting only on a single column. However, T-SQL allows to specify multiple PIVOT operators in the FROM clause, that way providing a solution for pivoting on multiple columns. There is one catch: the second PIVOT consumes the temporary result set output of the first PIVOT operator. Because of that the year column is not available (since it is used as pivoting column in the first PIVOT) and there is a need to define a virtual column that replicates the values for the year. Also, since the first PIVOT operator uses the year values as column names, the new virtual column subtracts 2000 from the year value to generate different column names for the second PIVOT operator. Here is the query using two PIVOT operators:
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;
In this particular case there is more elegant solution using a single PIVOT operator. With little math the value and the quantity can be combined into single column and then after the pivoting split back to two separate columns. Here is the solution using a single PIVOT operator:
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;