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

• 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).

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

• You cannot have duplicate pivot column values.

• 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.

• 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.

• 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 multiple pivot aggregate functions, they do not all need to be aggregating the same column.

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

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

Examples

The following SELECT statement produces a two column result set.

SELECT dayOfWeek, SUM(itemsProduced) 
FROM productionHistory 
GROUP BY dayOfWeek;
+-----------+--------------------+
| dayOfWeek | SUM(itemsProduced) |
+-----------+--------------------+
| Monday    |                 12 |
| Tuesday   |                 10 |
| Wednesday |                 24 |
| Thursday  |                  6 |
| Friday    |                 18 |
| Saturday  |                 15 |
| Sunday    |                 21 |
+-----------+--------------------+

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

SELECT "Items Produced" AS "Total Production by Day", Saturday, Sunday
FROM (
        SELECT dayOfWeek, itemsProduced 
        FROM productionHistory
) AS pvt_subquery
PIVOT (
        SUM(itemsProduced) 
        FOR dayOfWeek 
        IN ("Saturday", "Sunday")
) AS pvt_table;
+-------------------------+----------+--------+
| Total Production by Day | Saturday | Sunday |
+-------------------------+----------+--------+
| Items Produced          |       15 |     21 |
+-------------------------+----------+--------+

The following SELECT statement produces a three column result set from the same table as above.

SELECT dayOfWeek, numWorkers, SUM(itemsProduced) 
FROM productionHistory 
WHERE dayOfWeek 
IN ("Monday", "Tuesday") 
GROUP BY dayOfWeek, numWorkers;
+-----------+------------+--------------------+
| dayOfWeek | numWorkers | SUM(itemsProduced) |
+-----------+------------+--------------------+
| Monday    |          2 |                  7 |
| Monday    |          3 |                  5 |
| Tuesday   |          1 |                  1 |
| Tuesday   |          2 |                  2 |
| Tuesday   |          3 |                  7 |
+-----------+------------+--------------------+

This three column example can be pivoted as:

SELECT numWorkers, Monday, Tuesday
FROM (
        SELECT dayOfWeek, numWorkers, itemsProduced 
        FROM productionHistory
) AS pvt_subquery
PIVOT (
        SUM(itemsProduced) 
        FOR dayOfWeek 
        IN ("Monday", "Tuesday")
) as pvt_table;
+------------+--------+---------+
| numWorkers | Monday | Tuesday |
+------------+--------+---------+
|          1 |   NULL |       1 |
|          3 |      5 |       7 |
|          2 |      7 |       2 |
+------------+--------+---------+

Here is a similar example but with using multiple pivot aggregates:

SELECT numWorkers, Monday_SUM, Monday_AVG, Tuesday_SUM, Tuesday_AVG 
FROM (
        SELECT dayOfWeek, numWorkers, itemsProduced 
        FROM productionHistory
) pvt_subquery
PIVOT (
        SUM(itemsProduced) AS SUM, AVG(itemsProduced) AS AVG 
        FOR dayOfWeek 
        IN ("Monday", "Tuesday")
) pvt_table;
+------------+------------+------------+-------------+-------------+
| numWorkers | Monday_SUM | Monday_AVG | Tuesday_SUM | Tuesday_AVG |
+------------+------------+------------+-------------+-------------+
|          1 |       NULL |       NULL |           1 |      1.0000 |
|          2 |          7 |     3.5000 |           2 |      2.0000 |
|          3 |          5 |     1.6666 |           7 |      2.3333 |
+------------+------------+------------+-------------+-------------+

Was this Article Helpful?

Thanks For Your Feedback!