Categories:
Query Syntax

PIVOT

Rotates a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. In a query, it is specified in the FROM clause after the table name or subquery.

The operator supports the built-in aggregate functions AVG, COUNT, MAX, MIN, and SUM.

PIVOT can be used to transform a narrow table (e.g. empid, month, sales) into a wider table (e.g. empid, jan_sales, feb_sales, mar_sales).

See also:
UNPIVOT

Syntax

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )

[ ... ]
aggregate_function
The aggregate function for combining the grouped values from pivot_column.
pivot_column
The column from the source table or subquery that will be aggregated.
value_column
The column from the source table or subquery that contains the values from which column names will be generated.
pivot_value_N
A list of values for the pivot column to pivot into headings in the query results.

Examples

Given a table, monthly_sales, with the following structure, pivot around the amount column to sum the total sales per employee for the specified months:

CREATE OR REPLACE TABLE monthly_sales(empid INT, amount INT, month TEXT)
    AS SELECT * FROM VALUES
    (1, 10000, 'JAN'),
    (1, 400, 'JAN'),
    (2, 4500, 'JAN'),
    (2, 35000, 'JAN'),
    (1, 5000, 'FEB'),
    (1, 3000, 'FEB'),
    (2, 200, 'FEB'),
    (2, 90500, 'FEB'),
    (1, 6000, 'MAR'),
    (1, 5000, 'MAR'),
    (2, 2500, 'MAR'),
    (2, 9500, 'MAR'),
    (1, 8000, 'APR'),
    (1, 10000, 'APR'),
    (2, 800, 'APR'),
    (2, 4500, 'APR');

Query and output:

SELECT * 
  FROM monthly_sales
    PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
      AS p
  ORDER BY EMPID;
+-------+-------+-------+-------+-------+
| EMPID | 'JAN' | 'FEB' | 'MAR' | 'APR' |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
+-------+-------+-------+-------+-------+

If you prefer the column names without quotes, or if you prefer that the output have different column names than the input, you can include the column names in the AS clause, as shown below:

SELECT * 
  FROM monthly_sales
    PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
      AS p (EMP_ID, JAN, FEB, MAR, APR)
  ORDER BY EMP_ID;
+--------+-------+-------+-------+-------+
| EMP_ID |   JAN |   FEB |   MAR |   APR |
|--------+-------+-------+-------+-------|
|      1 | 10400 |  8000 | 11000 | 18000 |
|      2 | 39500 | 90700 | 12000 |  5300 |
+--------+-------+-------+-------+-------+

or:

SELECT EMPID AS EMP_ID, "'JAN'" AS JANUARY, "'FEB'" AS FEBRUARY, "'MAR'" AS MARCH,
    "'APR'" AS APRIL
  FROM monthly_sales
    PIVOT(sum(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR')) 
      AS p
  ORDER BY EMPID;
+--------+---------+----------+-------+-------+
| EMP_ID | JANUARY | FEBRUARY | MARCH | APRIL |
|--------+---------+----------+-------+-------|
|      1 |   10400 |     8000 | 11000 | 18000 |
|      2 |   39500 |    90700 | 12000 |  5300 |
+--------+---------+----------+-------+-------+