Share via

UNPIVOT clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above.

Transforms the rows of the preceding table_reference by rotating groups of columns into rows and collapsing the listed columns: A first new column holds the original column group names (or alias there-of) as values, this column is followed for a group of columns with the values of each column group.

Syntax

UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] { single_value | multi_value } ( value_column FOR unpivot_column IN ( { column_name [ column_alias ] } [, ...] ) ) [ table_alias ] single_value ( value_column FOR unpivot_column IN ( { column_name [ column_alias ] } [, ...] ) ) multi_value ( ( value_column [, ...] ) FOR unpivot_column IN ( { ( column_name [, ...] ) [ column_alias ] } [, ...] ) )

Parameters

  • INCLUDE NULLS or EXCLUDE NULLS

    Whether, or not to filter out rows with NULL in the value_column. The default is EXCLUDE NULLS.

  • value_column

    An unqualified column alias. This column will hold the values. The type of ech value_column is the least common type of the corresponding column_name column types.

  • unpivot_column

    An unqualified column alias. This column will hold the names of the rotated column_names or their column_aliass. The type of unpivot_column is STRING.

    In case of a multi value UNPIVOT the value will be the concatenation of the '_' separated column_names, if there is no column_alias.

  • column_name

    Identifies a column in relation which will be un-pivoted. The name may be qualified. All column_names must share a least-common type.

  • column_alias

    An optional name used in unpivot_column.

  • table_alias

    Optionally specifies a label for the resulting table. If the table_alias includes column_identifiers their number must match the number of columns produced by UNPIVOT.

Result

A temporary table of the following form:

  • All the columns from the table_reference except those named as column_names.
  • The unpivot_column of type STRING.
  • The value_columns of the least common types of their matching column_names.

Examples

- A single column UNPIVOT > CREATE OR REPLACE TEMPORARY VIEW sales(location, year, q1, q2, q3, q4) AS VALUES ('Toronto' , 2020, 100 , 80 , 70, 150), ('San Francisco', 2020, NULL, 20 , 50, 60), ('Toronto' , 2021, 110 , 90 , 80, 170), ('San Francisco', 2021, 70 , 120, 85, 105); > SELECT * FROM sales UNPIVOT INCLUDE NULLS (sales FOR quarter IN (q1 AS `Jan-Mar`, q2 AS `Apr-Jun`, q3 AS `Jul-Sep`, sales.q4 AS `Oct-Dec`)); location year quarter sales —------------ —--- —------ —----- Toronto 2020 Jan-Mar 100 Toronto 2020 Apr-Jun 80 Toronto 2020 Jul-Sep 70 Toronto 2020 Oct-Dec 150 San Francisco 2020 Jan-Mar null San Francisco 2020 Apr-Jun 20 San Francisco 2020 Jul-Sep 50 San Francisco 2020 Oct-Dec 60 Toronto 2021 Jan-Mar 110 Toronto 2021 Apr-Jun 90 Toronto 2021 Jul-Sep 80 Toronto 2021 Oct-Dec 170 San Francisco 2021 Jan-Mar 70 San Francisco 2021 Apr-Jun 120 San Francisco 2021 Jul-Sep 85 San Francisco 2021 Oct-Dec 105 -- This is equivalent to: > SELECT location, year, inline(arrays_zip(array('Jan-Mar', 'Apr-Jun', 'Jul-Sep', 'Oct-Dec'), array(q1 , q2 , q3 , q4))) AS (quarter, sales) FROM sales; - A multi column UNPIVOT > CREATE OR REPLACE TEMPORARY VIEW oncall (year, week, area , name1 , email1 , phone1 , name2 , email2 , phone2) AS VALUES (2022, 1 , 'frontend', 'Freddy', 'fred@alwaysup.org' , 15551234567, 'Fanny' , 'fanny@lwaysup.org' , 15552345678), (2022, 1 , 'backend' , 'Boris' , 'boris@alwaysup.org', 15553456789, 'Boomer', 'boomer@lwaysup.org', 15554567890), (2022, 2 , 'frontend', 'Franky', 'frank@lwaysup.org' , 15555678901, 'Fin' , 'fin@alwaysup.org' , 15556789012), (2022, 2 , 'backend' , 'Bonny' , 'bonny@alwaysup.org', 15557890123, 'Bea' , 'bea@alwaysup.org' , 15558901234); > SELECT * FROM oncall UNPIVOT ((name, email, phone) FOR precedence IN ((name1, email1, phone1) AS primary, (name2, email2, phone2) AS secondary)); year week area precedence name email phone ---- ---- -------- ---------- ------ ------------------ ----------- 2022 1 frontend primary Freddy fred@alwaysup.org 15551234567 2022 1 frontend secondary Fanny fanny@lwaysup.org 15552345678 2022 1 backend primary Boris boris@alwaysup.org 15553456789 2022 1 backend secondary Boomer boomer@lwaysup.org 15554567890 2022 2 frontend primary Franky frank@lwaysup.org 15555678901 2022 2 frontend secondary Fin fin@alwaysup.org 15556789012 2022 2 backend primary Bonny bonny@alwaysup.org 15557890123 2022 2 backend secondary Bea bea@alwaysup.org 15558901234 -- This is equivalent to: > SELECT year, week, area, inline(arrays_zip(array('primary', 'secondary'), array(name1, name2), array(email1, email2), array(phone1, phone2))) AS (precedence, name, email, phone) FROM oncall;