Categories:: Query syntax

PIVOT¶

Rotates a table by turning the unique values from one column in the input expression into multiple columns and aggregating results where required on any remaining column values. In a query, it is specified in the FROM clause after the table name or subquery.

The operator supports the built-in aggregate functions AVG, COUNT, MAX, MIN, and SUM.

PIVOT can be used to transform a narrow table (e.g. empid, month, sales) into a wider table (e.g. empid, jan_sales, feb_sales, mar_sales).

See also:: UNPIVOT

Syntax¶

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

Parameters¶

aggregate_function: The aggregate function for combining the grouped values from pivot_column.
pivot_column: The column from the source table or subquery that will be aggregated.
value_column: The column from the source table or subquery that contains the values from which column names will be generated.
pivot_value_N: A list of values for the pivot column to pivot into headings in the query results.
ANY [ ORDER BY ... ]: Pivot on all distinct values of the pivot column. To control the order of the pivot columns in the output, specify an ORDER BY clause after the ANY keyword. If the pivot column contains NULLs, then NULL is also treated as a pivot value.
subquery: Pivot on all values found in the subquery. The DISTINCT keyword is required if the subquery includes an ORDER BY clause. The subquery must be an uncorrelated subquery that returns a single column. Pivoting is performed on all distinct values returned by the subquery. For information about uncorrelated subqueries, see Working with Subqueries.
DEFAULT ON NULL (value): Replace all NULLs in the pivot result with the specified default value. The default value can be any scalar expression that does not depend on the pivot and aggregation column.

Usage notes¶

Snowflake supports dynamic pivot. A dynamic pivot query uses the ANY keyword or a subquery in the PIVOT subclause instead of specifying the pivot values explicitly.
When dynamic pivot is used in a view definition, queries on the view might fail if the underlying data changes so that the pivot output columns are changed.
Dynamic pivot is not supported in the body of a stored procedure or user-defined function (UDF).
A pivot query that does not use dynamic pivot can return output with duplicate columns. We recommend avoiding output with duplicate columns. A dynamic pivot query deduplicates duplicate columns.
A pivot query that does not use dynamic pivot might fail if it attempts to CAST a VARIANT column to a different data type. Dynamic pivot queries do not have this limitation.

Examples¶

The PIVOT examples use the following quarterly_sales table:

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 on all distinct column values automatically with dynamic pivot¶

Given the table quarterly_sales, pivot on the amount column using the ANY keyword to sum the total sales per employee for all of the distinct quarters, and specify ORDER BY so that the pivot columns are in order:

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 on column values using a subquery with dynamic pivot¶

Assume that in addition to the quarterly_sales table, an ad_campaign_types_by_quarter table tracks the types of advertisements run during particular quarters. This table has the following structure and data:

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

You can use a subquery in a pivot query to determine the sum of the sales in the quarters that had specific ad campaigns. For example, the following pivot query returns data only for quarters with television ad campaigns:

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 on a specified list of column values for the pivot column¶

Given the table quarterly_sales, pivot on the amount column to sum the total sales per employee for the specified quarters:

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

You can pivot on all of the quarters in the amount column by running the following query:

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

You can modify the column names in the output with the AS clause. For example, to shorten the column names and show them without quotes, run the following query:

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

You can also list specific columns in the SELECT list and change the column names:

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

If the query returns nulls, you can replace them with a default value. For example, the following query returns nulls for 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 |
+-------+-----------+-----------+-----------+-----------+-----------+

You can replace the nulls with a default value of 0 by running the following query:

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