Clause UNPIVOT

  • Article

S’applique à : coche marquée oui Databricks SQL coche pour oui Databricks Runtime 12.2 LTS et versions ultérieures.

Transforme les lignes de table_reference en faisant pivoter des groupes de colonnes en lignes et en réduisant les colonnes répertoriées : une première nouvelle colonne contient les noms de groupes de colonnes d’origine (ou leurs alias) en tant que valeurs. Cette colonne est suivie pour un groupe de colonnes avec les valeurs de chaque groupe de colonnes.

Syntaxe

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

Paramètres

  • table_reference

    Identifie le sujet de l’opération UNPIVOT.

  • INCLUDE NULLS ou EXCLUDE NULLS

    Indique s’il faut filtrer ou non les lignes avec NULL dans le value_column. Par défaut, il s’agit de EXCLUDE NULLS.

  • Colonne de valeur

    Alias de colonne non qualifié. Cette colonne contient les valeurs. Le type de chaque value_column est le type le moins commun des types de colonnes column_name correspondants.

  • unpivot_column

    Alias de colonne non qualifié. Cette colonne contient les noms des column_name pivotés ou de leurs column_alias. Le type de unpivot_column est STRING.

    Dans le cas d’une valeur UNPIVOT multiple, la valeur sera la concaténation des column_name séparés par '_', s’il n’y a pas de column_alias.

  • column_name

    Identifie une colonne par rapport qui ne sera pas pivotée. Le nom peut être qualifié. Tous les column_name doivent partager un type le moins commun.

  • column_alias

    Nom facultatif utilisé dans unpivot_column.

  • table_alias

    Spécifie éventuellement une étiquette pour la table résultante. Si le table_alias inclut column_identifier, leur nombre doit correspondre au nombre de colonnes produites par UNPIVOT.

Résultats

Une table temporaire au format suivant :

  • Toutes les colonnes du table_reference, à l’exception de celles nommées en tant que column_name.
  • Le unpivot_column de type STRING.
  • Les value_column des types les moins courants de leurs column_name correspondants.

Exemples

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