cláusula UNPIVOT

Se aplica a: Databricks SQL Databricks Runtime 12.2 LTS y versiones posteriores.

Transforma las filas de la tabla de referencia anterior rotando grupos de columnas en filas y consolidando las columnas enumeradas: una primera nueva columna contiene como valores los nombres originales de los grupos de columnas (o sus alias), seguida de un grupo de columnas con los valores de cada grupo de columnas.

Sintaxis

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

Parámetros

• INCLUDE NULLS o EXCLUDE NULLS

  Indica si las filas con NULL en value_column se van a filtrar o no. El valor predeterminado es EXCLUDE NULLS.

• value_column

  Alias de columna no calificado. Esta columna contendrá los valores. El tipo de cada value_column es el tipo menos común de los tipos de columnas column_name correspondientes.

• unpivot_column

  Alias de columna no calificado. Esta columna contendrá los nombres de todos los elementos column_name girados o sus column_alias. El tipo de unpivot_column es STRING.

  En el caso de un valor UNPIVOT múltiple, el valor será la concatenación de los elementos '_' separados por column_name, si no hay column_alias.

• column_name

  Identifica una columna relacionada cuya dinamización se anulará. El nombre puede ser calificado. Todos los elementos column_name deben compartir un tipo menos común.

• column_alias

  Nombre opcional usado en unpivot_column.

• table_alias

  De manera opcional, especifica una etiqueta para la tabla resultante. Si table_alias incluye column_identifier, su número debe coincidir con el número de columnas generadas por UNPIVOT.

Resultado

Una tabla temporal con el formato siguiente:

• Todas las columnas de la table_reference, excepto las denominadas como column_name.
• El unpivot_column de tipo STRING.
• Los elementos value_column de los tipos menos comunes de sus elementos column_name coincidentes.

Ejemplos

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

Artículos relacionados

• Cláusula PIVOT
• SELECT