Cloudera Docs

UNPIVOT clause

The UNPIVOT clause transforms structured data sets by merging multiple columns into a single data column while generating a corresponding header column for the original source attributes.

The UNPIVOT clause is a query operator that allows you to reorganize data by merging multiple columns from a table into a single data column. This process creates a more normalized result set by rotating columns into rows, making the data easier to aggregate or analyze.

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

  • The query engine merges specified target columns from the source table into a single data column.
  • The result table includes a new header column that identifies the original source column for every row.
  • All original columns that are not part of the UNPIVOT definition are retained in the result set.
  • For each row in the source table, the operation generates one row in the result table for every specified unpivot column.

Syntax:

The following syntax block shows the structure of an UNPIVOT operation:

SELECT columns FROM table_name UNPIVOT ( data_column FOR header_column IN (column_x AS 'label_x', column_y AS 'label_y', ...) ) [AS result_alias];

Usage notes:

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

  • The value specified in the AS clause is written to the header column to help you identify the original source column.
  • You cannot directly resolve or select the original source columns after performing the UNPIVOT operation.
  • If an unpivot column contains NULL values, those values are included in the result set by default unless a predicate is applied to filter them.

Examples:

The following example shows an UNPIVOT operation that merges two integer columns into one:

SELECT * FROM s UNPIVOT (a FOR b IN (x AS 'x', y AS 'y')) AS t;

In this example:

  • The UNPIVOT clause merges the unpivot columns x and y from source table s.
  • All data from columns x and y is placed into the data column a in the result table t.
  • The header column b contains the string labels 'x' or 'y' to indicate the source of the data for each row.
  • The result table t contains the data column a, the header column b, and all other columns from table s.