PIVOT
On this page
The PIVOT
operation lets you transform nonaggregated 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 builtin aggregate functions as well as userdefined aggregate functions in your PIVOT
clause.
Syntax
SELECT nonpivoted_col, firstpivoted_col, secondpivoted_col[, ..., lastpivoted_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 anotherSELECT
subquery.To do this joining, wrap the PIVOT
operator in an additional level ofSELECT
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 nonpivoted column must all be distinct.

The
PIVOT
subquery can be any validSELECT
statement, but it must project at least two columns.
If exactly two columns are projected from the subquery, the nonpivoted 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 nonpivoted 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 nonpivoted columns where at one of the pivoted columns has a nonnull 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 theWHERE
clause of aSELECT
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 asval1
,val2
, andval3
, 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 piv_production (week varchar(10),day ENUM('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY'),numWorkers INT,itemsProduced INT);INSERT INTO piv_production VALUES('Week 1', 1,2,4),('Week 1', 2,11,15),('Week 1', 3,10,12),('Week 1', 4,11,7),('Week 1', 5,10,12),('Week 1', 6,11,10),('Week 1', 7,5,7),('Week 2', 1,4,5),('Week 2', 2,8,10),('Week 2', 3,8,11),('Week 2', 4,7,9),('Week 2', 5,11,8),('Week 2', 6,11,12),('Week 2', 7,6,8);SELECT * FROM piv_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 twocolumn result set.
SELECT day, SUM(itemsProduced)FROM piv_productionGROUP 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, TuesdayFROM ( SELECT day, itemsProducedFROM piv_production) AS pivotSubQueryPIVOT (SUM(itemsProduced)FOR dayIN ("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.
Example 2: Projecting Three Columns
The following SELECT
statement produces a three column result set:
SELECT day, week, SUM(itemsProduced)FROM piv_productionWHERE dayIN ("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, TuesdayFROM ( SELECT day, week, itemsProducedFROM piv_production) AS pivotSubQueryPIVOT ( SUM(itemsProduced)FOR dayIN ("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, Day2FROM ( SELECT day, week, itemsProducedFROM piv_production) AS pivotSubQueryPIVOT ( SUM(itemsProduced)FOR dayIN ("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_AVGFROM ( SELECT day, week, itemsProducedFROM piv_production) AS pivotSubQueryPIVOT ( SUM(itemsProduced) AS SUM, AVG(itemsProduced) AS AVGFOR dayIN ("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.
Last modified: March 1, 2023