Limitations of UNPIVOT clause

Functional query constraints, data type requirements, and workaround strategies for UNPIVOT operations in Impala SQL.

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

Mismatched data types

All target columns must share an identical data type. If your columns have different data types, you can cast them to a matching data type to complete the operation successfully.

Workaround: Cast the columns to the same data type in a Common Table Expression (CTE), then apply UNPIVOT to the CTE.

Run the following query to align data types:

with t1 (a, v1, v2) as (
   values (1, 2, 3.0)
), t1_casted as (
   select
       a,
       cast(v1 as decimal(2, 1)) as v1,
       cast(v2 as decimal(2, 1)) as v2
   from t1
)
select * from t1_casted unpivot (
   c for b in (v1 as 'v1', v2 as 'v2')
) as t;

+---+-----+----+
| a | c   | b  |
+---+-----+----+
| 1 | 2.0 | v1 |
| 1 | 3.0 | v2 |
+---+-----+----+
Fetched 2 row(s) in 0.11s

Path expression duplication

Each UNPIVOT clause must use a unique path expression because Impala does not support duplicate path expressions across multiple UNPIVOT clauses in the same query block.

Workaround: Isolate duplicate path expressions by wrapping one of the table references inside a CTE or subquery with a unique alias.

Run the following query to isolate the path expressions:

with table_same_path as (
   select month from functional_parquet.alltypestiny
)
select t1.b, t1.c, t2.b, t2.c
from table_same_path unpivot (
   c for b in (month as 'm')
) as t1, functional_parquet.alltypestiny unpivot (
   c for b in (month as 'm')
) as t2
where t1.c = 1 and t1.c = t2.c;

+---+---+---+---+
| b | c | b | c |
+---+---+---+---+
| m | 1 | m | 1 |
| m | 1 | m | 1 |
| m | 1 | m | 1 |
| m | 1 | m | 1 |
+---+---+---+---+
Fetched 4 row(s) in 0.11s