Posts

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;

Dynamic Pivoting

SQL Server 2005 added the PIVOT operator which makes creating cross-tab queries very easy. However, as of this writing none of the SQL Server versions has built-in support for dynamic pivoting. The PIVOT operator only provides basic pivoting capabilities on a static list of values. In practice it is very often needed to perform this for dynamic list of values.

Here is one solution for dynamic pivoting that uses the ability in SQL Server 2005/2008 to concatenate row values using FOR XML PATH with blank element name. This method performs a query on the distinct values to pivot and creates a column list based on that. Then the resulting column list is used in a dynamic query utilizing the PIVOT operator and executed as dynamic SQL.

In this scenario the goal is to pivot order amounts by month.

-- Table with orders

CREATE TABLE Orders (

  order_id INT NOT NULL PRIMARY KEY,

  order_date DATETIME NOT NULL

            DEFAULT CURRENT_TIMESTAMP,

  amount DECIMAL(8, 2) NOT NULL DEFAULT 0

        CHECK (amount >= 0));

 

-- Sample data

INSERT INTO Orders

(order_id, order_date, amount)

SELECT 1, '20070101', 10.50

UNION ALL

SELECT 2, '20070126', 12.50

UNION ALL

SELECT 3, '20070130', 12.00

UNION ALL

SELECT 4, '20070214', 13.75

UNION ALL

SELECT 5, '20070220', 10.00

UNION ALL

SELECT 6, '20070306', 15.00

UNION ALL

SELECT 7, '20070310', 17.50

UNION ALL

SELECT 8, '20070329', 20.00;

 

-- Build list of column values to pivot

DECLARE @cols NVARCHAR(1000);

SELECT @cols =

STUFF((SELECT N'],[' + year_month

       FROM (SELECT DISTINCT CONVERT(NCHAR(7), order_date, 126)

            FROM Orders) AS O(year_month)

       ORDER BY year_month

       FOR XML PATH('')

      ), 1, 2, '') + N']';

 

-- Build dynamic SQL query for pivoting  

DECLARE @sql NVARCHAR(2000);

SET @sql =

N'SELECT order_year, ' + @cols +

N'FROM (SELECT DATEPART(yyyy, order_date) AS order_year, ' +

            N'CONVERT(NCHAR(7), order_date, 126) AS year_month, ' +

            N'amount ' +

      N'FROM Orders) AS O ' +

N'PIVOT ' +

N'(SUM(amount) FOR year_month IN (' + @cols + N')) AS P;';

 

EXEC(@sql);

 

/*

 

Results:

 

order_year 2007-01 2007-02 2007-03

----------- -------- -------- --------

2007        35.00    23.75    52.50

 

*/

This query can be further improved by using the QUOTENAME function to prevent any possibility of SQL injection.

Unpivoting Data

Unpivoting data is a very useful technique for normalizing denormalized tables. It refers to the process of transposing multiple columns across into rows in a single column. In other words, taking the data below:

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

and converting to look like this:

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

Here are different methods to achieve that:

-- 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;

It is good no note that unpivoting multiple columns (like amount1, date1, amount2, date2 to amount and date columns) is not supported by the SQL Server 2005 UNPIVOT operator. This can be achieved only by using the first two methods.

Additional Resources:

Using PIVOT and UNPIVOT
http://msdn2.microsoft.com/en-us/library/ms177410.aspx

Pivoting data in SQL Server

Very often there is a need to pivot (cross-tab) normalized data for some reporting purposes. While this is best done with reporting tools (Excel is one example with powerful pivoting capabilities), sometimes it needs to be done on the database side.

The discussion here is limited to static pivoting (that is when the values to pivot are known). Dynamic pivoting is accomplished by using dynamic SQL and is based on the same techniques, just dynamically building the pivoting query.

Here are a few methods and techniques to implement that in SQL Server.

Given the following data:

 OrderId    OrderDate  Amount
----------- ---------- ------
1 2007-01-01 10.50
2 2007-01-26 12.50
3 2007-01-30 12.00
4 2007-02-14 13.75
5 2007-02-20 10.00
6 2007-03-06 15.00
7 2007-03-10 17.50
8 2007-03-29 20.00

We would like to return this result set:

 OrderYear   Jan   Feb   Mar
----------- ----- ----- -----
2007 35.00 23.75 52.50

Creating the sample Orders table:

CREATE TABLE Orders (

 order_id INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,

 order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

 amount DECIMAL(8, 2) NOT NULL DEFAULT 0

        CHECK (amount >= 0));

 

INSERT INTO Orders

  (order_date, amount)

SELECT '20070101', 10.50

UNION ALL

SELECT '20070126', 12.50

UNION ALL

SELECT '20070130', 12.00

UNION ALL

SELECT '20070214', 13.75

UNION ALL

SELECT '20070220', 10.00

UNION ALL

SELECT '20070306', 15.00

UNION ALL

SELECT '20070310', 17.50

UNION ALL

SELECT '20070329', 20.00;

Using CASE

CASE can be very handy for pivoting. It allows writing flexible logic and to transform the data as needed. Also, it is a very efficient method as it requires a one pass scan of the data.

SELECT DATEPART(yyyy, order_date) AS OrderYear,

       SUM(CASE WHEN DATEPART(m, order_date) = 1

                THEN amount ELSE 0 END) AS 'Jan',

       SUM(CASE WHEN DATEPART(m, order_date) = 2

                THEN amount ELSE 0 END) AS 'Feb',

       SUM(CASE WHEN DATEPART(m, order_date) = 3

                THEN amount ELSE 0 END) AS 'Mar'

FROM Orders

GROUP BY DATEPART(yyyy, order_date);

Using Matrix table

This method requires creating a matrix table with 1 for the significant columns and 0 for columns to be ignored. Then joining the matrix table to the data table based on the pivoting column will produce the results:

CREATE TABLE MonthMatrix (

  month_nbr INT NOT NULL PRIMARY KEY

            CHECK (month_nbr BETWEEN 1 AND 12),

  jan INT NOT NULL DEFAULT 0

      CHECK (jan IN (0, 1)),

  feb INT NOT NULL DEFAULT 0

      CHECK (feb IN (0, 1)),

  mar INT NOT NULL DEFAULT 0

      CHECK (mar IN (0, 1)),

  apr INT NOT NULL DEFAULT 0

      CHECK (apr IN (0, 1)),

  may INT NOT NULL DEFAULT 0

      CHECK (may IN (0, 1)),

  jun INT NOT NULL DEFAULT 0

      CHECK (jun IN (0, 1)),

  jul INT NOT NULL DEFAULT 0

      CHECK (jul IN (0, 1)),

  aug INT NOT NULL DEFAULT 0

      CHECK (aug IN (0, 1)),

  sep INT NOT NULL DEFAULT 0

      CHECK (sep IN (0, 1)),

  oct INT NOT NULL DEFAULT 0

      CHECK (oct IN (0, 1)),

  nov INT NOT NULL DEFAULT 0

      CHECK (nov IN (0, 1)),

  dec INT NOT NULL DEFAULT 0

      CHECK (dec IN (0, 1)));

 

-- Populate the matrix table

INSERT INTO MonthMatrix (month_nbr, jan) VALUES (1, 1);

INSERT INTO MonthMatrix (month_nbr, feb) VALUES (2, 1);

INSERT INTO MonthMatrix (month_nbr, mar) VALUES (3, 1);

INSERT INTO MonthMatrix (month_nbr, apr) VALUES (4, 1);

INSERT INTO MonthMatrix (month_nbr, may) VALUES (5, 1);

INSERT INTO MonthMatrix (month_nbr, jun) VALUES (6, 1);

INSERT INTO MonthMatrix (month_nbr, jul) VALUES (7, 1);

INSERT INTO MonthMatrix (month_nbr, aug) VALUES (8, 1);

INSERT INTO MonthMatrix (month_nbr, sep) VALUES (9, 1);

INSERT INTO MonthMatrix (month_nbr, oct) VALUES (10, 1);

INSERT INTO MonthMatrix (month_nbr, nov) VALUES (11, 1);

INSERT INTO MonthMatrix (month_nbr, dec) VALUES (12, 1);

 

-- Use the matrix table to pivot

SELECT DATEPART(yyyy, order_date) AS OrderYear,

       SUM(amount * jan) AS 'Jan',

       SUM(amount * feb) AS 'Feb',

       SUM(amount * mar) AS 'Mar'

FROM Orders AS O

JOIN MonthMatrix AS M

  ON DATEPART(m, O.order_date) = M.month_nbr

GROUP BY DATEPART(yyyy, order_date);

The David Rozenshtein method

This method is based on the formula: 1 – ABS(SIGN(x – y)). This formula returns 1 when x = y, and 0 otherwise. In a way it mimics the matrix table approach.

SELECT DATEPART(yyyy, order_date) AS OrderYear,

       SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 1)))) AS 'Jan',

       SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 2)))) AS 'Feb',

       SUM(amount * (1 - ABS(SIGN(DATEPART(m, order_date) - 3)))) AS 'Mar'

FROM Orders

GROUP BY DATEPART(yyyy, order_date);

The PIVOT operator in SQL Server 2005

SQL Server 2005 provides built-in pivoting mechanism via the PIVOT operator.

SELECT OrderYear,

       [1] AS 'Jan',

       [2] AS 'Feb',

       [3] AS 'Mar'

FROM (SELECT DATEPART(yyyy, order_date),

            DATEPART(m, order_date),

            amount

      FROM Orders) AS O (OrderYear, month_nbr, amount)

PIVOT

(SUM(amount) FOR month_nbr IN ([1], [2], [3])) AS P;

There are some other methods to implement pivoting (like using subqueries, multiple joins, or the APPLY operator in SQL Server 2005) but I am not showing examples of those as I do not find them practical, especially with large number of values to pivot.

Additional Resources:

Using PIVOT and UNPIVOT
http://msdn2.microsoft.com/en-us/library/ms177410.aspx