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

[ ... ]
Copy
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');
Copy

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 |
+-------+-------+-------+-------+-------+
Copy

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_renamed, JAN, FEB, MAR, APR)
  ORDER BY EMP_ID_renamed;
+----------------+-------+-------+-------+-------+
| EMP_ID_RENAMED |   JAN |   FEB |   MAR |   APR |
|----------------+-------+-------+-------+-------|
|              1 | 10400 |  8000 | 11000 | 18000 |
|              2 | 39500 | 90700 | 12000 |  5300 |
+----------------+-------+-------+-------+-------+
Copy

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 |
+--------+---------+----------+-------+-------+
Copy