Limitations of PIVOT clause

Technical restrictions, functional constraints, and workaround strategies for PIVOT operations in Impala SQL.

The PIVOT clause contains specific technical restrictions and functional constraints when running query operations.

Multiple header columns

The PIVOT clause does not support the use of multiple header columns.

Workaround: Combine the multiple columns into a new column in a CTE, then use the combined column as the header column.

Example

with combine_year_month as (
   select concat(concat(cast(year as string), '_'), cast(month as string)) as year_month
   from functional_parquet.alltypestiny
)
select * from combine_year_month pivot (
   count(*) for year_month in ('2009_1', '2009_2', '2009_3', '2009_4')
) as t;

+--------+--------+--------+--------+
| 2009_1 | 2009_2 | 2009_3 | 2009_4 |
+--------+--------+--------+--------+
| 2      | 2      | 2      | 2      |
+--------+--------+--------+--------+
Fetched 1 row(s) in 0.23s

Complex source table columns

Referencing complex columns in the source table within a PIVOT clause is not supported.

Workaround: None.

Root cause: A GROUP BY expression cannot be used on complex types without specifying a field.

Nested fields as header columns

Specifying a nested field as the header column is not supported.

Workaround: Select the field into a new column in a CTE, then use the new column as the header column.

Example

with select_ti as (
   select alltypes.ti as ti
   from functional_parquet.complextypes_structs
)
select * from select_ti pivot (
   count(*) for ti in (90, 100, 123, 127)
) as t;

+----+-----+-----+-----+
| 90 | 100 | 123 | 127 |
+----+-----+-----+-----+
| 1  | 1   | 1   | 1   |
+----+-----+-----+-----+
Fetched 1 row(s) in 0.11s

Constant expressions in header values

Specifying constant expressions other than literals in the header value list is not supported.

Workaround: Evaluate the constant expressions before writing the SQL statement in the host language such as Python, and then use the result directly in SQL.

Non-SELECT statements

Using the PIVOT clause in statements other than SELECT is not supported.

Workaround: Fetch the result of PIVOT using the client and then insert it into a table with another SQL statement.