PIVOT

Limitations of Pivot Columns

• You cannot use an expression as a pivot column value.

• You cannot have duplicate pivot column values.

• You can specify distinct aliases for pivot column values.

• There are no restrictions about where the pivoted columns may appear in the PIVOT operation project list. They may appear out of order, multiple pivot columns may appear in the same project list item as part of an expression, or a pivoted column may not appear at all. The PIVOT operator project list item also can be aliased.

• The pivoted column, the aggregated column, and the non-pivoted column must all be distinct.

• The PIVOT subquery can be any valid SELECT statement, but it must project at least two columns.

  • If exactly two columns are projected from the subquery, the non-pivoted column in the pivot operation project list must be a constant.

  • If three or more columns are projected from the subquery, the columns that are not the aggregated column and that are not the pivot column will become non-pivoted columns that can be accessed in the PIVOT operator project list. There will be a row in the PIVOT operator result set for every unique combination of values from the non-pivoted columns where at one of the pivoted columns has a non-null value.

Using Aggregate Functions

• You can have multiple pivot aggregate functions in the same PIVOT operation.

• If you have multiple pivot aggregates, they must each have a unique alias. A pivoted column will be created for each pivot aggregate and pivot column value combination. These pivoted columns will have their normal names appended by an underscore character with the alias of the pivot aggregate. An example is given in the Examples section below.

• If you have only one pivot aggregate, it cannot have an alias.

• If you have multiple pivot aggregate functions, they do not all need to be aggregating the same column.

Escaping Rules for Pivot Columns

• When constructing the pivot column values clause, use the same escaping rules for each pivot column values that you would use if you were constructing a normal IN list that would go in the WHERE clause of a SELECT query. An example pivot column values clause for a string pivot column would be FOR col_text IN ("val1", "val2", "val3"). An example pivot column values clause for a integer pivot column would be FOR col_int IN (1, 2, 3).

• When referring to a pivoted column in the construction of the project list of a PIVOT operation, use the same escaping rules that you would use if a normal table column had the same name as a pivot column value.

  In general, this means escaping the pivoted columns with the ` character. Using the example in the first bullet point above, you can refer to the string pivot columns values (val1, val2, val3) in the project list of the pivot operation without quotes just as val1, val2, and val3, respectively. However, when you refer to the integer pivot column values in the PIVOT operator project, they must be escaped as `0`, `1`, and `2`, respectively.

Example 1: Projecting Two Columns

The following SELECT statement produces a two-column result set.

SELECT day, SUM(itemsProduced)
FROM piv_production
GROUP BY day ORDER BY day;

+-----------+--------------------+
| day       | SUM(itemsProduced) |
+-----------+--------------------+
| SUNDAY    |                  9 |
| MONDAY    |                 25 |
| TUESDAY   |                 23 |
| WEDNESDAY |                 16 |
| THURSDAY  |                 20 |
| FRIDAY    |                 22 |
| SATURDAY  |                 15 |
+-----------+--------------------+

By pivoting on values in the day column, the above SELECT statement can be presented as:

SELECT "Items Produced" AS "Total Production by Day", Monday, Tuesday
FROM ( SELECT day, itemsProduced
       FROM piv_production
     ) AS pivotSubQuery
PIVOT (
        SUM(itemsProduced)
        FOR day
        IN ("MONDAY", "TUESDAY")
      ) AS pivotTable;

+-------------------------+--------+---------+
| Total Production by Day | Monday | Tuesday |
+-------------------------+--------+---------+
| Items Produced          |     25 |      23 |
+-------------------------+--------+---------+

In this example only two columns are projected in the pivot subquery. Hence, the non-pivoted column is a constant.

Example 2: Projecting Three Columns

The following SELECT statement produces a three column result set:

SELECT day, week, SUM(itemsProduced)
FROM piv_production
WHERE day
IN ("MONDAY", "TUESDAY")
GROUP BY day, week;

+---------+--------+--------------------+
| day     | week   | SUM(itemsProduced) |
+---------+--------+--------------------+
| MONDAY  | Week 1 |                 15 |
| MONDAY  | Week 2 |                 10 |
| TUESDAY | Week 1 |                 12 |
| TUESDAY | Week 2 |                 11 |
+---------+--------+--------------------+

This three column example can be pivoted on the day column as:

SELECT week, Monday, Tuesday
FROM (  SELECT day, week, itemsProduced
        FROM piv_production
     ) AS pivotSubQuery
PIVOT ( SUM(itemsProduced)
        FOR day
        IN ("MONDAY", "TUESDAY")
      ) as pivotTable;

+--------+--------+---------+
| week   | Monday | Tuesday |
+--------+--------+---------+
| Week 1 |     15 |      12 |
| Week 2 |     10 |      11 |
+--------+--------+---------+

Example 3: Specifying Aliases for Pivoted Columns

The following example shows how to specify aliases for the pivoted columns:

SELECT week As WeekCount, Day1, Day2
FROM (  SELECT day, week, itemsProduced
        FROM piv_production
     ) AS pivotSubQuery
PIVOT ( SUM(itemsProduced)
        FOR day
        IN ("MONDAY" AS Day1, "TUESDAY" AS Day2)
      ) as pivotTable;

+-----------+------+------+
| WeekCount | Day1 | Day2 |
+-----------+------+------+
| Week 1    |   15 |   12 |
| Week 2    |   10 |   11 |
+-----------+------+------+

Example 4: Using Multiple Pivot Aggregates

The following example shows how to use multiple pivot aggregates:

SELECT week, Monday_SUM, Monday_AVG, Tuesday_SUM, Tuesday_AVG
FROM ( SELECT day, week, itemsProduced
       FROM piv_production
     ) AS pivotSubQuery
PIVOT ( SUM(itemsProduced) AS SUM, AVG(itemsProduced) AS AVG
        FOR day
        IN ("MONDAY", "TUESDAY")
      ) AS pivotTable;

+--------+------------+------------+-------------+-------------+
| week   | Monday_SUM | Monday_AVG | Tuesday_SUM | Tuesday_AVG |
+--------+------------+------------+-------------+-------------+
| Week 1 |         15 |    15.0000 |          12 |     12.0000 |
| Week 2 |         10 |    10.0000 |          11 |     11.0000 |
+--------+------------+------------+-------------+-------------+

Note that the name of each pivoted column is appended by an underscore and the alias of the pivot aggregate.