类别：

查询语法

PIVOT

通过将输入表达式中的一列中的唯一值转换为多列并根据需要聚合任何剩余列值的结果来旋转表。在查询中，它在表名或子查询后面的 FROM 子句中指定。

该运算符支持内置聚合函数 AVG、COUNT、MAX、MIN 和 SUM

PIVOT 可用于将窄表（例如 empid、month、sales）转换为更宽的表（例如 empid、jan_sales、feb_sales、mar_sales）。

另请参阅：

UNPIVOT

语法

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
aggregate_function

用于组合来自 pivot_column 的分组值的聚合函数。

pivot_column

源表或子查询中将被聚合的列。

value_column

源表或子查询中的列，其中包含将用于生成列名的值。

pivot_value_N

将数据透视列的值列表转换为查询结果中的标题。

ANY [ ORDER BY ... ]

功能支持的区域

此功能在中华人民共和国不可用。

对透视列的所有不同值执行透视。要控制输出中透视列的顺序，请在 ANY 关键字后指定 ORDER BY 子句。如果透视列包含 NULLs，则 NULL 也被视为透视值。

subquery

功能支持的区域

此功能在中华人民共和国不可用。

对在子查询中找到的所有值执行透视。如果子查询包含 ORDER BY 子句，则需要 DISTINCT 关键字。子查询必须是返回单个列的不相关子查询。对子查询返回的所有不同值执行透视。有关不相关子查询的信息，请参阅 使用子查询。

DEFAULT ON NULL (value)

用指定的默认值替换透视结果中的所有 NULLs。默认值可以是任何不依赖于透视和聚合列的标量表达式。

使用说明

  • Snowflake 支持 动态透视。动态透视查询使用 ANY 关键字或 PIVOT 分子句中的子查询，而不是显式指定透视值。

  • 不使用动态透视的透视查询可以返回含有重复列的输出。我们建议避免含有重复列的输出。动态透视查询会对重复列进行去重。

  • 如果不使用动态透视的透视查询尝试执行以下操作，则可能会失败：将 VARIANT 列 CAST 为不同的数据类型。动态透视查询没有此限制。

示例

PIVOT 示例使用以下 quarterly_sales 表：

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

使用动态透视自动对所有不同的列值执行透视

给定表 quarterly_sales，使用 ANY 关键字来计算所有不同季度每位员工的总销售额之和，以对 amount 列执行透视，并通过指定 ORDER BY 使透视列按顺序排列：

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

使用具有动态透视的子查询对列值执行透视

假设除了 quarterly_sales 表之外，ad_campaign_types_by_quarter 表也在跟踪在特定季度投放的广告的类型。该表具有以下结构和数据：

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

您可以在透视查询中使用子查询来确定开展了特定广告活动的季度的销售额总和。例如，以下透视查询仅返回开展了电视广告活动的季度的数据：

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

对透视列的指定列值列表执行透视

给定表 quarterly_sales，对 amount 列执行透视，以计算指定季度每个员工的总销售额之和：

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

通过运行以下查询，可以对 amount 列中的所有季度执行透视：

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

您可以使用 AS 子句修改输出中的列名。例如，要缩短列名并显示不带引号的列名，请运行以下查询：

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

您还可以在 SELECT 列表中列出特定列，并更改列名：

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

如果查询返回 null 值，则可以用默认值替换这些值。例如，以下查询为 2024_Q1 返回 null 值：

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

通过运行以下查询，您可以将这些 null 值替换为默认值 0：

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 |
+-------+-----------+-----------+-----------+-----------+-----------+
语言: 中文