类别：: 查询语法

UNPIVOT¶

通过将列转换为行来旋转表。UNPIVOT 是一个关系运算符，它接受两列（来自表或子查询）以及列的列表，并为列表中指定的每列生成一行。在查询中，它在表名或子查询后面的 FROM 子句中指定。

UNPIVOT 与 PIVOT 并非完全相反，因为它无法撤销 PIVOT 生成的聚合。

此运算符可用于将宽表（例如 empid、jan_sales、feb_sales、mar_sales）转换为较窄的表（例如 empid、month、sales）。

另请参阅：: PIVOT

语法¶

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

[ ... ]

Copy

参数¶

{ INCLUDE | EXCLUDE } NULLS

指定包含还是排除 name_column 中为 NULLs 的行：

INCLUDE NULLS 包含为 NULLs 的行。
EXCLUDE NULLS 不包含为 NULLs 的行。

默认：EXCLUDE NULLS

value_column

要分配给生成的列的名称，该列将使用列的列表中各列的值进行填充。

name_column

要分配给生成的列的名称，该列将使用列的列表中的列名称进行填充。

column_list

源表或子查询中将轮换为单个透视列的列的名称。列名称将填充 name_column，列值将填充 value_column。

column_list 仅可包含字面量列名称，不能包含子查询。

column_list 中的列必须具有完全相同的数据类型，但以下情况除外：

文本字符串的数据类型长度可以不同。
如果列包含文本字符串，则不同的列可以使用不同的文本数据类型。例如，该列表可以包括一个 VARCHAR 列和一个 CHAR 列。

[ AS ] col_alias

指定要在 UNPIVOT 操作的结果中使用的列别名，而不是原始列名。不能对同一列名使用不同的别名。但是，不能对多个列名使用相同的别名。AS 关键字是可选的。

示例¶

创建一个名为 monthly_sales 的表，其结构和数据如下：

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

取消透视各个月份列，以按 month 为每个雇员返回单独一个 sales 值：

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

以下示例与前面的示例相同，但它使用列名的别名：

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

Copy

+-------+-------------+----------+-------+
| EMPID | DEPT        | MONTH    | SALES |
|-------+-------------+----------+-------|
|     1 | electronics | JANUARY  |   100 |
|     1 | electronics | FEBRUARY |   200 |
|     1 | electronics | MARCH    |   300 |
|     1 | electronics | APRIL    |   100 |
|     2 | clothes     | JANUARY  |   100 |
|     2 | clothes     | FEBRUARY |   300 |
|     2 | clothes     | MARCH    |   150 |
|     2 | clothes     | APRIL    |   200 |
|     3 | cars        | JANUARY  |   200 |
|     3 | cars        | FEBRUARY |   400 |
|     3 | cars        | MARCH    |   100 |
|     3 | cars        | APRIL    |    50 |
|     4 | appliances  | JANUARY  |   100 |
|     4 | appliances  | MARCH    |   100 |
|     4 | appliances  | APRIL    |    50 |
+-------+-------------+----------+-------+

The previous SELECT statements exclude NULLs by default. So, they don't include a row for appliances in February in the results. To include NULLs in the results, run the following SQL statement:

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

该输出包含 2 月份的家电数据行。

无需使用 * 选择所有列，您可以在 SELECT 列表中包含特定列，并引用 UNPIVOT value_column 和 name_column。下面的示例与前面的示例类似，但在 SELECT 列表中指定了 value_column sales 和 name_column month。查询不包含 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 |
+-------------+-------+-------+