UNPIVOT-Klausel

Gilt für: Databricks SQL Databricks Runtime 12.2 LTS und höher.

Transformiert die Zeilen der vorherigen table_reference, indem Spaltengruppen zu Zeilen gedreht und die aufgelisteten Spalten zusammengefasst werden: Eine erste neue Spalte enthält die ursprünglichen Spaltengruppennamen (oder deren Aliase) als Werte, und diese Spalte wird gefolgt von einer Gruppe von Spalten, die die Werte jeder Spaltengruppe enthält.

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 ] } [, ...] ) )

Parameter

• INCLUDE NULLS oder EXCLUDE NULLS

  Gibt an, ob Zeilen mit NULL in der value_column gefiltert werden sollen. Der Standardwert lautet EXCLUDE NULLS.

• value_column

  Ein nicht qualifizierter Spaltenalias. Diese Spalte enthält die Werte. Der Typ jeder value_column ist der geringste gemeinsame Typ der entsprechenden column_name-Spaltentypen.

• unpivot_column

  Ein nicht qualifizierter Spaltenalias. Diese Spalte enthält die Namen der rotierten column_names oder ihrer column_aliase. Der Typ von unpivot_column ist STRING.

  Im Fall eines UNPIVOT mit mehreren Werten ist der Wert die Verkettung der durch '_' getrennten column_names, wenn kein column_alias vorhanden ist.

• column_name

  Identifiziert eine Spalte in der Beziehung, die entpivotiert wird. Der Name kann qualifiziert sein. Alle column_names müssen einen geringsten gemeinsamen Typ aufweisen.

• column_alias

  Ein in unpivot_column verwendeter optionaler Name.

• table_alias

  Gibt optional eine Bezeichnung für die resultierende Tabelle an. Wenn der table_aliascolumn_identifiers enthält, muss deren Anzahl mit der Anzahl der von UNPIVOT erzeugten Spalten übereinstimmen.

Ergebnis

Eine temporäre Tabelle im folgenden Format:

• Alle Spalten aus der table_reference außer den als column_names benannten.
• Die unpivot_column vom Typ STRING.
• Die value_columns der geringsten gemeinsamen Typen ihrer übereinstimmenden column_names.

Beispiele

- 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;

Verwandte Artikel

• PIVOT Klausel
• SELECT