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 PIVOT prend en charge les fonctions d’agrégation intégrées suivantes :

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> ) [ [ AS ] <alias> ]
            FOR <value_column> IN (
              <pivot_value_1> [ [ AS ] <alias> ] [ , <pivot_value_2> [ [ AS ] <alias> ] ... ]
              | ANY [ ORDER BY ... ]
              | <subquery>
            )
            [ DEFAULT ON NULL (<value>) ]
         )

[ ... ]

Paramètres

aggregate_function

Fonction d’agrégation permettant de combiner les valeurs groupées à partir de pivot_column.

pivot_column [ [ AS ] alias ]

La colonne de la table source ou de la sous-requête qui sera agrégée.

La clause [ AS ] alias facultative spécifie l’alias à utiliser pour l’agrégat dans le résultat de l’opération PIVOT. Un tiret bas, puis l’alias est ajouté à chaque nom de colonne pivot. Par exemple, si l’alias est total, alors l’opération pivot ajoute _TOTAL aux noms des colonnes pivot. Le mot clé AS est facultatif.

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 [ [ AS ] alias ]

Liste de valeurs permettant à la colonne pivot de pivoter dans les en-têtes des résultats de la requête.

La clause [ AS ] alias facultative spécifie l’alias à utiliser pour la valeur dans le résultat de l’opération PIVOT. L’alias remplace la valeur.

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 NULL 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.

  • Actuellement, la sémantique PIVOT n’autorise pas les agrégations multiples, mais vous pouvez obtenir des résultats similaires en utilisant PIVOT avec l’opérateur UNION. Pour un exemple, voir Pivot dynamique avec agrégations multiples à l’aide de l’opérateur UNION.

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'),
    (3, 2700, '2023_Q3'),
    (1, 8000, '2023_Q4'),
    (1, 10000, '2023_Q4'),
    (2, 800, '2023_Q4'),
    (2, 4500, '2023_Q4'),
    (3, 2700, '2023_Q4'),
    (3, 16000, '2023_Q4'),
    (3, 10200, '2023_Q4');

L’exemple suivant utilise PIVOT :

Pivot dynamique sur toutes les valeurs de colonne distinctes automatiquement

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;

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

L’exemple suivant est le même que l’exemple précédent, mais il ajoute l’alias _TOTAL à chaque nom de colonne pivot :

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

+-------+-----------------+-----------------+-----------------+-----------------+
| EMPID | '2023_Q1_TOTAL' | '2023_Q2_TOTAL' | '2023_Q3_TOTAL' | '2023_Q4_TOTAL' |
|-------+-----------------+-----------------+-----------------+-----------------|
|     1 |           10400 |            8000 |           11000 |           18000 |
|     2 |           39500 |           90700 |           12000 |            5300 |
|     3 |            NULL |            NULL |            2700 |           28900 |
+-------+-----------------+-----------------+-----------------+-----------------+

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

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

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;

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

Pivot dynamique avec agrégations multiples à l’aide de l’opérateur UNION

Vous pouvez utiliser l’opérateur UNION pour afficher plusieurs agrégations dans un seul jeu de résultats. Cet exemple utilise le pivot dynamique et l’opérateur UNION pour afficher les informations suivantes pour chaque employé et chaque trimestre :

  • Le montant moyen d’une vente, en utilisant la fonction AVG.

  • La vente dont la valeur est la plus élevée, en utilisant la fonction MAX.

  • La vente dont la valeur est la plus basse, en utilisant la fonction MIN.

  • Le nombre de ventes, en utilisant la fonction COUNT.

  • Le montant total de toutes les ventes, en utilisant la fonction SUM.

Exécutez la requête :

SELECT 'Average sale amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(AVG(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Highest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MAX(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Lowest value sale' AS aggregate, *
  FROM quarterly_sales
    PIVOT(MIN(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Number of sales' AS aggregate, *
  FROM quarterly_sales
    PIVOT(COUNT(amount) FOR quarter IN (ANY ORDER BY quarter))
UNION
SELECT 'Total amount' AS aggregate, *
  FROM quarterly_sales
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
ORDER BY aggregate, empid;

+---------------------+-------+--------------+--------------+--------------+--------------+
| AGGREGATE           | EMPID |    '2023_Q1' |    '2023_Q2' |    '2023_Q3' |    '2023_Q4' |
|---------------------+-------+--------------+--------------+--------------+--------------|
| Average sale amount |     1 |  5200.000000 |  4000.000000 |  5500.000000 |  9000.000000 |
| Average sale amount |     2 | 19750.000000 | 45350.000000 |  6000.000000 |  2650.000000 |
| Average sale amount |     3 |         NULL |         NULL |  2700.000000 |  9633.333333 |
| Highest value sale  |     1 | 10000.000000 |  5000.000000 |  6000.000000 | 10000.000000 |
| Highest value sale  |     2 | 35000.000000 | 90500.000000 |  9500.000000 |  4500.000000 |
| Highest value sale  |     3 |         NULL |         NULL |  2700.000000 | 16000.000000 |
| Lowest value sale   |     1 |   400.000000 |  3000.000000 |  5000.000000 |  8000.000000 |
| Lowest value sale   |     2 |  4500.000000 |   200.000000 |  2500.000000 |   800.000000 |
| Lowest value sale   |     3 |         NULL |         NULL |  2700.000000 |  2700.000000 |
| Number of sales     |     1 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     2 |     2.000000 |     2.000000 |     2.000000 |     2.000000 |
| Number of sales     |     3 |     0.000000 |     0.000000 |     1.000000 |     3.000000 |
| Total amount        |     1 | 10400.000000 |  8000.000000 | 11000.000000 | 18000.000000 |
| Total amount        |     2 | 39500.000000 | 90700.000000 | 12000.000000 |  5300.000000 |
| Total amount        |     3 |         NULL |         NULL |  2700.000000 | 28900.000000 |
+---------------------+-------+--------------+--------------+--------------+--------------+

Pivot dynamique avec une requête de jointure

Pour pivoter dans une requête avec jointure, vous pouvez utiliser une expression de table commune (CTE) pour la requête pivot.

Par exemple, supposons qu’une table simple mappe les employés aux gestionnaires :

CREATE OR REPLACE TABLE emp_manager(
    empid INT,
    managerid INT)
  AS SELECT * FROM VALUES
    (1, 7),
    (2, 8),
    (3, 9);

SELECT * from emp_manager;

+-------+-----------+
| EMPID | MANAGERID |
|-------+-----------|
|     1 |         7 |
|     2 |         8 |
|     3 |         9 |
+-------+-----------+

Exécuter une requête qui joint la table emp_manager et la table quarterly_sales et qui pivote sur la colonne amount de la table quarterly_sales :

WITH
  src AS
  (
    SELECT *
      FROM quarterly_sales
        PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  )
SELECT em.managerid, src.*
  FROM emp_manager em
  JOIN src ON em.empid = src.empid
  ORDER BY empid;

+-----------+-------+-----------+-----------+-----------+-----------+
| MANAGERID | EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-----------+-------+-----------+-----------+-----------+-----------|
|         7 |     1 |     10400 |      8000 |     11000 |     18000 |
|         8 |     2 |     39500 |     90700 |     12000 |      5300 |
|         9 |     3 |      NULL |      NULL |      2700 |     28900 |
+-----------+-------+-----------+-----------+-----------+-----------+

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;

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

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;

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

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' AS q1,
      '2023_Q2' AS q2,
      '2023_Q3' AS q3,
      '2023_Q4' AS q4))
  ORDER BY empid;

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

Pivot avec une valeur par défaut pour les valeurs NULL

Si la requête renvoie des valeurs NULL, vous pouvez les remplacer par une valeur par défaut en utilisant DEFAULT ON NULL. Par exemple, vous pouvez utiliser le pivot dynamique et remplacer les valeurs NULL par une valeur par défaut 0 en exécutant la requête suivante :

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

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

Vous pouvez également utiliser DEFAULT ON NULL avec une liste de colonnes spécifiée :

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

+-------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' |
|-------+-----------+-----------|
|     1 |     10400 |      8000 |
|     2 |     39500 |     90700 |
|     3 |         0 |         0 |
+-------+-----------+-----------+

Exemples de pivots qui impliquent plusieurs colonnes

Les requêtes pivot peuvent fonctionner avec plusieurs colonnes. Avant d’exécuter ces exemples, ajoutez une colonne à la table quarterly_sales et remplissez la colonne avec des valeurs aléatoires.

Tout d’abord, ajoutez à la table quarterly_sales une colonne qui suit la remise appliquée à chaque vente :

ALTER TABLE quarterly_sales ADD COLUMN discount_percent INT DEFAULT 0;

Remplir la nouvelle colonne avec des valeurs aléatoires comprises entre 0 et 5, qui indiquent le pourcentage de remise pour chaque vente :

UPDATE quarterly_sales SET discount_percent = UNIFORM(0, 5, RANDOM());

Interroger la table pour afficher la nouvelle colonne avec les valeurs aléatoires ajoutées :

SELECT * FROM quarterly_sales;

+-------+--------+---------+------------------+
| EMPID | AMOUNT | QUARTER | DISCOUNT_PERCENT |
|-------+--------+---------+------------------|
|     1 |  10000 | 2023_Q1 |                0 |
|     1 |    400 | 2023_Q1 |                1 |
|     2 |   4500 | 2023_Q1 |                4 |
|     2 |  35000 | 2023_Q1 |                2 |
|     1 |   5000 | 2023_Q2 |                2 |
|     1 |   3000 | 2023_Q2 |                1 |
|     2 |    200 | 2023_Q2 |                2 |
|     2 |  90500 | 2023_Q2 |                1 |
|     1 |   6000 | 2023_Q3 |                1 |
|     1 |   5000 | 2023_Q3 |                3 |
|     2 |   2500 | 2023_Q3 |                1 |
|     2 |   9500 | 2023_Q3 |                3 |
|     3 |   2700 | 2023_Q3 |                1 |
|     1 |   8000 | 2023_Q4 |                1 |
|     1 |  10000 | 2023_Q4 |                4 |
|     2 |    800 | 2023_Q4 |                3 |
|     2 |   4500 | 2023_Q4 |                5 |
|     3 |   2700 | 2023_Q4 |                3 |
|     3 |  16000 | 2023_Q4 |                0 |
|     3 |  10200 | 2023_Q4 |                1 |
+-------+--------+---------+------------------+

Maintenant que la nouvelle colonne est ajoutée et remplie, exécutez les exemples suivants :

Exclure des colonnes d’une requête pivot à l’aide de l’expression CTE

Vous pouvez utiliser une expression de table commune (CTE) pour exclure des colonnes d’une requête pivot.

L’exemple suivant utilise l’expression CTE pour exclure la colonne discount_percent d’une requête pivot :

WITH
  sales_without_discount AS
    (SELECT * EXCLUDE(discount_percent) FROM quarterly_sales)
SELECT *
  FROM sales_without_discount
    PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

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

Vous pouvez utiliser l’expression CTE pour exclure la colonne amount et afficher la remise moyenne accordée par chaque employé au cours de chaque trimestre :

WITH
  sales_without_amount AS
    (SELECT * EXCLUDE(amount) FROM quarterly_sales)
SELECT *
  FROM sales_without_amount
    PIVOT(AVG(discount_percent) FOR quarter IN (ANY ORDER BY quarter))
  ORDER BY empid;

+-------+-----------+-----------+-----------+-----------+
| EMPID | '2023_Q1' | '2023_Q2' | '2023_Q3' | '2023_Q4' |
|-------+-----------+-----------+-----------+-----------|
|     1 |  0.500000 |  1.500000 |  2.000000 |  2.500000 |
|     2 |  3.000000 |  1.500000 |  2.000000 |  4.000000 |
|     3 |      NULL |      NULL |  1.000000 |  1.333333 |
+-------+-----------+-----------+-----------+-----------+

Exécuter une requête pivot multidimensionnelle

Une requête pivot multidimensionnelle pivote sur plus d’une colonne. Cet exemple pivote sur la colonne amount et la colonne discount_percentage. La requête renvoie la somme de toutes les ventes réalisées par tous les employés chaque trimestre et le pourcentage de remise maximum pour toutes les ventes chaque trimestre.

Dans la requête, la liste SELECT utilise les paramètres $col_position pour exécuter les fonctions SUM et MAX sur les colonnes renvoyées dans l’ordre, et pour nommer les colonnes renvoyées. Une sous-requête dans la clause FROM fournit les données pour les opérations de pivot. Comme la sortie affiche les résultats des ventes pour tous les employés, la sous-requête n’inclut pas la colonne empid.

SELECT SUM($1) AS q1_sales_total,
       SUM($2) AS q2_sales_total,
       SUM($3) AS q3_sales_total,
       SUM($4) AS q4_sales_total,
       MAX($5) AS q1_maximum_discount,
       MAX($6) AS q2_maximum_discount,
       MAX($7) AS q3_maximum_discount,
       MAX($8) AS q4_maximum_discount
  FROM
    (SELECT amount,
            quarter AS quarter_amount,
            quarter AS quarter_discount,
            discount_percent
      FROM quarterly_sales)
  PIVOT (
    SUM(amount)
    FOR quarter_amount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'))
  PIVOT (
    MAX(discount_percent)
    FOR quarter_discount IN (
      '2023_Q1',
      '2023_Q2',
      '2023_Q3',
      '2023_Q4'));

+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+
| Q1_SALES_TOTAL | Q2_SALES_TOTAL | Q3_SALES_TOTAL | Q4_SALES_TOTAL | Q1_MAXIMUM_DISCOUNT | Q2_MAXIMUM_DISCOUNT | Q3_MAXIMUM_DISCOUNT | Q4_MAXIMUM_DISCOUNT |
|----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------|
|          49900 |          98700 |          25700 |          52200 |                   4 |                   2 |                   3 |                   5 |
+----------------+----------------+----------------+----------------+---------------------+---------------------+---------------------+---------------------+