Catégories :: Syntaxe de requête

PIVOT¶

Permet de faire pivoter une table en transformant les valeurs uniques d’une colonne de l’expression d’entrée en plusieurs colonnes et en agrégeant les résultats, le cas échéant, sur les valeurs d’une colonne restante. Dans une requête, il est spécifié dans la clause FROM après le nom de la table ou la sous-requête.

L’opérateur prend en charge les fonctions d’agrégat intégrées AVG, COUNT, MAX, MIN, et SUM.

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

Voir aussi :: UNPIVOT

Syntaxe¶

SELECT ...
FROM ...
   PIVOT ( <aggregate_function> ( <pivot_column> )
            FOR <value_column> IN (
              <pivot_value_1> [ , <pivot_value_2> ... ]
              | ANY [ ORDER BY ... ]
              | <subquery>
            )
            [ DEFAULT ON NULL (<value>) ]
         )

[ ... ]

Copy

Paramètres¶

aggregate_function: Fonction d’agrégation permettant de combiner les valeurs groupées à partir de pivot_column.
pivot_column: La colonne de la table source ou de la sous-requête qui sera agrégée.
value_column: La colonne de la table source ou de la sous-requête contenant les valeurs à partir desquelles les noms de colonne seront générés.
pivot_value_N: Liste de valeurs permettant à la colonne pivot de pivoter dans les en-têtes des résultats de la requête.
ANY [ ORDER BY ... ]: Pivot sur toutes les valeurs distinctes de la colonne pivot. Pour contrôler l’ordre des colonnes pivot de la sortie, spécifiez une clause ORDER BY après le mot-clé ANY. Si la colonne pivot contient des valeurs NULLs, la valeur NULL est également traitée comme une valeur pivot.
subquery: Pivot sur toutes les valeurs trouvées dans la sous-requête. Le mot-clé DISTINCT est obligatoire si la sous-requête inclut une clause ORDER BY. La sous-requête doit être une sous-requête non corrélée qui renvoie une seule colonne. La mise en pivot est effectuée sur toutes les valeurs distinctes renvoyées par la sous-requête. Pour des informations sur les sous-requêtes non corrélées, voir Utilisation des sous-requêtes.
DEFAULT ON NULL (value): Remplacez toutes les valeurs NULLs du résultat pivot par la valeur par défaut spécifiée. La valeur par défaut peut être n’importe quelle expression scalaire qui ne dépend pas du pivot ni de la colonne d’agrégation.

Notes sur l’utilisation¶

Snowflake prend en charge le pivot dynamique. Une requête pivot dynamique utilise le mot-clé ANY ou une sous-requête dans la sous-clause PIVOT au lieu de spécifier explicitement les valeurs pivot.
Lorsque le pivot dynamique est utilisé dans une définition de vue, les requêtes sur la vue peuvent échouer si les données sous-jacentes changent de sorte que les colonnes de sortie du pivot sont modifiées.
Le pivot dynamique n’est pas pris en charge dans le corps d’une procédure stockée ou d’une fonction définie par l’utilisateur (UDF).
Une requête pivot qui n’utilise pas de pivot dynamique peut renvoyer une sortie avec des colonnes en double. Nous vous recommandons d’éviter les sorties avec des colonnes en double. Une requête pivot dynamique dédoublonne les colonnes en double.
Une requête pivot qui n’utilise pas de pivot dynamique risque d’échouer si elle tente d’appliquer une opération CAST à une colonne VARIANT pour la convertir en un autre type de données. Les requêtes pivot dynamiques n’ont pas cette limitation.

Exemples¶

Les exemples de PIVOT utilisent la table quarterly_sales suivante :

CREATE OR REPLACE TABLE quarterly_sales(
  empid INT, 
  amount INT, 
  quarter TEXT)
  AS SELECT * FROM VALUES
    (1, 10000, '2023_Q1'),
    (1, 400, '2023_Q1'),
    (2, 4500, '2023_Q1'),
    (2, 35000, '2023_Q1'),
    (1, 5000, '2023_Q2'),
    (1, 3000, '2023_Q2'),
    (2, 200, '2023_Q2'),
    (2, 90500, '2023_Q2'),
    (1, 6000, '2023_Q3'),
    (1, 5000, '2023_Q3'),
    (2, 2500, '2023_Q3'),
    (2, 9500, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4');

Copy

Pivot sur toutes les valeurs de colonne distinctes automatiquement avec un pivot dynamique¶

Dans la table quarterly_sales, appliquez un pivot sur la colonne amount à l’aide du mot-clé ANY pour additionner les ventes totales par employé pour tous les trimestres distincts, et spécifiez ORDER BY pour que les colonnes pivot soient dans l’ordre :

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

Pivot sur les valeurs de colonne à l’aide d’une sous-requête avec un pivot dynamique¶

Supposez qu’en plus de la table quarterly_sales, une table ad_campaign_types_by_quarter suive les types de publicités diffusées au cours de certains trimestres. Cette table présente la structure et les données suivantes :

CREATE OR REPLACE TABLE ad_campaign_types_by_quarter(
  quarter VARCHAR,
  television BOOLEAN,
  radio BOOLEAN,
  print BOOLEAN)
  AS SELECT * FROM VALUES
    ('2023_Q1', TRUE, FALSE, FALSE),
    ('2023_Q2', FALSE, TRUE, TRUE),
    ('2023_Q3', FALSE, TRUE, FALSE),
    ('2023_Q4', TRUE, FALSE, TRUE);

Copy

Vous pouvez utiliser une sous-requête dans une requête pivot pour déterminer la somme des ventes des trimestres qui ont fait l’objet de campagnes publicitaires spécifiques. Par exemple, la requête pivot suivante renvoie les données uniquement pour les trimestres ayant fait l’objet de campagnes publicitaires à la télévision :

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      SELECT DISTINCT quarter
        FROM ad_campaign_types_by_quarter
        WHERE television = TRUE
        ORDER BY quarter)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q4' |
|-------+-----------+-----------|
|     1 |     10400 |     18000 |
|     2 |     39500 |      5300 |
+-------+-----------+-----------+

Pivot sur une liste spécifiée de valeurs de colonne pour la colonne pivot¶

Dans la table quarterly_sales, appliquez un pivot sur la colonne amount pour additionner les ventes totales par employé pour les trimestres spécifiés :

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' |
|-------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |
|     2 |     39500 |     90700 |     12000 |
+-------+-----------+-----------+-----------+

Vous pouvez appliquer un pivot sur tous les trimestres de la colonne amount en exécutant la requête suivante :

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |
|     2 |     39500 |     90700 |     12000 |      5300 |
+-------+-----------+-----------+-----------+-----------+

Vous pouvez modifier les noms de colonne de la sortie à l’aide de la clause AS. Par exemple, pour raccourcir les noms de colonne et les afficher sans guillemets, exécutez la requête suivante :

SELECT * 
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) AS p (empid_renamed, Q1, Q2, Q3, Q4)
  ORDER BY empid_renamed;

Copy

+---------------+-------+-------+-------+-------+
| EMPID_RENAMED |    Q1 |    Q2 |    Q3 |    Q4 |
|---------------+-------+-------+-------+-------|
|             1 | 10400 |  8000 | 11000 | 18000 |
|             2 | 39500 | 90700 | 12000 |  5300 |
+---------------+-------+-------+-------+-------+

Vous pouvez également répertorier des colonnes spécifiques dans la liste SELECT et modifier les noms de colonne :

SELECT empid, 
       "'2023_Q1'" AS Q1, 
       "'2023_Q2'" AS Q2, 
       "'2023_Q3'" AS Q3,
       "'2023_Q4'" AS Q4
  FROM quarterly_sales
    PIVOT(sum(amount) FOR quarter IN (
      '2023_Q1', 
      '2023_Q2', 
      '2023_Q3', 
      '2023_Q4')
    ) 
  ORDER BY empid;

Copy

+-------+-------+-------+-------+-------+
| EMPID |    Q1 |    Q2 |    Q3 |    Q4 |
|-------+-------+-------+-------+-------|
|     1 | 10400 |  8000 | 11000 | 18000 |
|     2 | 39500 | 90700 | 12000 |  5300 |
+-------+-------+-------+-------+-------+

Si la requête renvoie des valeurs nulles, vous pouvez les remplacer par une valeur par défaut. Par exemple, la requête suivante renvoie des valeurs nulles pour 2024_Q1 :

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |      NULL |
|     2 |     39500 |     90700 |     12000 |      5300 |      NULL |
+-------+-----------+-----------+-----------+-----------+-----------+

Vous pouvez remplacer les valeurs nulles par une valeur par défaut 0 en exécutant la requête suivante :

SELECT *
  FROM quarterly_sales
    PIVOT(SUM(amount)
      FOR quarter IN (
        '2023_Q1',
        '2023_Q2',
        '2023_Q3',
        '2023_Q4',
        '2024_Q1')
      DEFAULT ON NULL (0)
    )
  ORDER BY empid;

Copy

+-------+-----------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' | '2024_Q1' |
|-------+-----------+-----------+-----------+-----------+-----------|
|     1 |     10400 |      8000 |     11000 |     18000 |         0 |
|     2 |     39500 |     90700 |     12000 |      5300 |         0 |
+-------+-----------+-----------+-----------+-----------+-----------+