Cloudera Docs

PIVOT clause

Impala SQL queries can use the PIVOT clause to transform unique column values into multiple new columns for horizontal data aggregation.

The PIVOT clause is a query operator that allows you to reorganize data by turning unique values from one column into multiple new columns. This process involves applying an aggregate function to the data and redistributing the results across these new column headers.

You can use the PIVOT clause to summarize data into a grid format such as a calendar or a spreadsheet with totals calculated at the intersection of rows and columns. While a standard GROUP BY statement aggregates data vertically into a single column of rows, the PIVOT clause splits aggregation results horizontally across multiple columns. This operation transforms a flat, repeating table into a multi-dimensional summary or cross-tab report.

When tracking team sales, calculating total revenue requires evaluating two dimensions, such as the team member who completed the sale (Employees) and the corresponding time period (Months). You can represent this scenario using either of the following approaches:

  • Standard GROUP BY approach (vertical) — This operation returns a long, single column of results. You must scroll down through repeating names to compare different months.
  • PIVOT approach (grid) — This operation places the employees down the left side and the months across the top header, which allows for immediate side-by-side comparison.

When you apply the PIVOT clause to a table reference, the following changes occur:

  • The operation identifies unique values in a designated header column.
  • Each identified value becomes a separate column in the final output.
  • The result table includes the newly generated columns and all original columns from the source table, excluding the header column.
  • The aggregation process calculates values for each intersection of the remaining rows and the new columns.

Syntax:

The following syntax block shows the structure of a PIVOT operation:

SELECT columns FROM table_name
PIVOT (
  aggregate_function_1(target_column) [as alias_1], aggregate_function_2(target_column) [as alias_2], ...
  FOR header_column IN (value_1 [AS alias_1], value_2 [AS alias_2], ...)
) [AS result_alias];

Usage notes:

When you use the PIVOT clause, keep the following details in mind:

  • You must provide aliases when you specify multiple aggregate expressions in a single PIVOT clause.
  • The query uses all columns that are not part of the PIVOT definition to group the rows.
  • The header column is removed from the final display because its data is represented in the new column headers.
  • If the pivot column contains NULL values, the resulting pivot columns for those specific rows display NULL unless the aggregation handles them otherwise.

Examples:

The following example shows a PIVOT operation that uses a count aggregation:

SELECT * FROM s PIVOT ( count(a) for b in (1 AS v1, 2 AS v2) ) AS t;

In this example:

  • The PIVOT clause splits the aggregation column count(a) into two columns.
  • The v1 column contains the aggregation results after b = 1.
  • The v2 column contains the aggregation results after b = 2.
  • The result table t contains the v1 and v2 columns created from the split.
  • The result table t contains all columns in the source table s except for column b.
  • Column b is the header column because its values are used for the header of the result table to identify the columns created from the split.

The following example shows how to use the PIVOT clause with DISTINCT aggregation to count unique months:

SELECT year, v1, v2 FROM functional_parquet.alltypestiny PIVOT (
count(distinct month) FOR month IN (1 AS v1, 2 AS v2)
) AS t;

The following example shows how to use PIVOT within a Common Table Expression (CTE) to handle values from a UNION node:

WITH t1 (a, b) AS (
VALUES (1, 2), (3, 4)
)
SELECT a, v2, v4 FROM t1 PIVOT (
min(b) FOR b IN (2 AS v2, 4 AS v4)
) AS t;