Catégories :: Syntaxe de requête

UNPIVOT¶

Fait pivoter une table en transformant des colonnes en lignes. UNPIVOT est un opérateur relationnel qui accepte deux colonnes (d’une table ou d’une sous-requête), ainsi qu’une liste de colonnes, et génère une ligne pour chaque colonne spécifiée dans la liste. Dans une requête, il est spécifié dans la clause FROM après le nom de la table ou la sous-requête.

UNPIVOT n’est pas exactement l’inverse de PIVOT, car il ne peut pas annuler les agrégations effectuées par PIVOT.

Cet opérateur peut être utilisé pour transformer une table large (par exemple empid, jan_sales, feb_sales, mar_sales) en une table plus étroite (par exemple empid, month, sales).

Voir aussi :: PIVOT

Syntaxe¶

SELECT ...
FROM ...
    UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]
      ( <value_column>
        FOR <name_column> IN ( <column_list> ) )

[ ... ]

Copy

Paramètres¶

{ INCLUDE | EXCLUDE } NULLS

Spécifie s’il faut inclure ou exclure les lignes contenant des valeurs NULLs dans name_column :

INCLUDE NULLS inclut les lignes avec des valeurs NULLs.
EXCLUDE NULLS exclut les lignes avec des valeurs NULLs.

Par défaut : EXCLUDE NULLS

value_column

Nom à affecter à la colonne générée qui sera renseignée avec les valeurs des colonnes de la liste des colonnes.

name_column

Le nom à affecter à la colonne générée qui sera renseignée avec les noms des colonnes de la liste des colonnes.

column_list

Noms des colonnes de la table ou de la sous-requête source qui seront pivotées en une seule colonne pivot. Les noms de colonnes rempliront name_column et les valeurs de colonne rempliront value_column.

column_list ne peut contenir que des noms de colonne littéraux, et non une sous-requête.

Exemples¶

Créez une table, monthly_sales, avec la structure et les données suivantes :

CREATE OR REPLACE TABLE monthly_sales(
  empid INT,
  dept TEXT,
  jan INT,
  feb INT,
  mar INT,
  apr INT
);

INSERT INTO monthly_sales VALUES
  (1, 'electronics', 100, 200, 300, 100),
  (2, 'clothes', 100, 300, 150, 200),
  (3, 'cars', 200, 400, 100, 50),
  (4, 'appliances', 100, NULL, 100, 50);

SELECT * FROM monthly_sales;

Copy

+-------+-------------+-----+------+------+-----+
| EMPID | DEPT        | JAN | FEB  | MAR  | APR |
|-------+-------------+-----+------+------+-----|
|     1 | electronics | 100 | 200  | 300  | 100 |
|     2 | clothes     | 100 | 300  | 150  | 200 |
|     3 | cars        | 200 | 400  | 100  |  50 |
|     4 | appliances  | 100 | NULL | 100  |  50 |
+-------+-------------+-----+------+------+-----+

Dépivotez les colonnes de mois individuelles pour renvoyer une seule valeur sales par month pour chaque employé.

SELECT *
  FROM monthly_sales
    UNPIVOT (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

L’instruction SELECT précédente exclut les valeurs NULLs par défaut. Elle n’inclut donc pas de ligne pour les appareils en février dans les résultats. Pour inclure les valeurs NULLs dans les résultats, exécutez l’instruction SQL suivante :

SELECT *
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY empid;

Copy

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APR   |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APR   |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APR   |    50 |
|     4 | appliances  | JAN   |   100 |
|     4 | appliances  | FEB   |  NULL |
|     4 | appliances  | MAR   |   100 |
|     4 | appliances  | APR   |    50 |
+-------+-------------+-------+-------+

Cette sortie inclut une ligne pour les appareils en février.

Au lieu de sélectionner toutes les colonnes avec *, vous pouvez inclure des colonnes spécifiques dans la liste SELECT et référencer UNPIVOT value_column et name_column. L’exemple suivant est similaire à l’exemple précédent, mais il spécifie value_column sales et name_column month dans la liste SELECT. La requête exclut la colonne empid :

SELECT dept, month, sales
  FROM monthly_sales
    UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
  ORDER BY dept;

Copy

+-------------+-------+-------+
| DEPT        | MONTH | SALES |
|-------------+-------+-------|
| appliances  | JAN   |   100 |
| appliances  | FEB   |  NULL |
| appliances  | MAR   |   100 |
| appliances  | APR   |    50 |
| cars        | JAN   |   200 |
| cars        | FEB   |   400 |
| cars        | MAR   |   100 |
| cars        | APR   |    50 |
| clothes     | JAN   |   100 |
| clothes     | FEB   |   300 |
| clothes     | MAR   |   150 |
| clothes     | APR   |   200 |
| electronics | JAN   |   100 |
| electronics | FEB   |   200 |
| electronics | MAR   |   300 |
| electronics | APR   |   100 |
+-------------+-------+-------+