- Categories:
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:
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_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 | +----------------+-------+-------+-------+-------+
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 | +--------+---------+----------+-------+-------+