Outdated Version

You are viewing an older version of this section. View current production version.

PIVOT

The PIVOT operation lets you transform non-aggregated data into a pivot table output format. This allows you to output pivoted data to a table, text, or report without using a secondary tool. PIVOT rotates a table by turning the unique values from one column in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values.

You can use built-in aggregate functions as well as user-defined aggregate functions in your PIVOT clause.

Syntax

SELECT non-pivoted_col, first-pivoted_col, second-pivoted_col[, ..., last-pivoted_col]
FROM
(
    pivot_select_subquery
) [AS] subquery_alias
PIVOT
(
    aggregate_function(col_from_subquery)
    FOR pivot_col_from_subquery IN (first_pivot_col_value, second_pivot_col_value[, ..., last_pivot_col_value])
) [AS] pivot_result_alias

Remarks

A new query plan is generated for each new combination of pivot column values.
The result of a PIVOT operator cannot be directly joined with a table or the results of another SELECT subquery. To do this joining, wrap the PIVOT operator in an additional level of SELECT nesting. For example:
```
SELECT * FROM (pivot_operation) q1 JOIN (other_subquery) q2;
```

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 only one pivot aggregate, it cannot have an alias.
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 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.

Examples

The following examples use the production table:

CREATE TABLE production (
	day ENUM('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'),
  numWorkers INT,
  itemsProduced INT);

SELECT * FROM production;
****
+--------+-----------+------------+---------------+
| week   | day       | numWorkers | itemsProduced |
+--------+-----------+------------+---------------+
| Week 1 | SUNDAY    |          2 |             4 |
| Week 1 | MONDAY    |         11 |            15 |
| Week 1 | TUESDAY   |         10 |            12 |
| Week 1 | WEDNESDAY |         11 |             7 |
| Week 1 | THURSDAY  |         10 |            12 |
| Week 1 | FRIDAY    |         11 |            10 |
| Week 1 | SATURDAY  |          5 |             7 |
| Week 2 | SUNDAY    |          4 |             5 |
| Week 2 | MONDAY    |          8 |            10 |
| Week 2 | TUESDAY   |          8 |            11 |
| Week 2 | WEDNESDAY |          7 |             9 |
| Week 2 | THURSDAY  |         11 |             8 |
| Week 2 | FRIDAY    |         11 |            12 |
| Week 2 | SATURDAY  |          6 |             8 |
+--------+-----------+------------+---------------+

Example 1: Projecting Two Columns

The following SELECT statement produces a two column result set.

SELECT day, SUM(itemsProduced)
FROM 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 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 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 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 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 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.

Was this Article Helpful?

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.