- 类别：
UNPIVOT¶
通过将列转换为行来旋转表。UNPIVOT 是一个关系运算符，它接受两列（来自表或子查询）以及列的列表，并为列表中指定的每列生成一行。在查询中，它在表名或子查询后面的 FROM 子句中指定。
UNPIVOT 与 PIVOT 并非完全相反，因为它无法撤消 PIVOT 生成的聚合。
此运算符可用于将宽表（例如 empid、jan_sales、feb_sales、mar_sales）转换为较窄的表（例如 empid、month、sales）。
- 另请参阅：
语法¶
SELECT ...
FROM ...
UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ]
( <value_column>
FOR <name_column> IN ( <column_list> ) )
[ ... ]
参数¶
{ INCLUDE | EXCLUDE } NULLS指定包含还是排除
name_column中为 NULLs 的行：INCLUDE NULLS包含为 NULLs 的行。EXCLUDE NULLS不包含为 NULLs 的行。
默认：
EXCLUDE NULLSvalue_column要分配给生成的列的名称，该列将使用列的列表中各列的值进行填充。
name_column要分配给生成的列的名称，该列将使用列的列表中的列名称进行填充。
column_list源表或子查询中将轮换为单个透视列的列的名称。列名称将填充
name_column，列值将填充value_column。column_list仅可包含字面量列名称，不能包含子查询。
示例¶
创建一个名为 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;
+-------+-------------+-----+------+------+-----+
| 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;
+-------+-------------+-------+-------+
| 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 语句默认不包括 NULLs。因此，结果中不包括针对 2 月份的家电数据的行。要在结果中包含 NULLs，请运行以下 SQL 语句：
SELECT *
FROM monthly_sales
UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar, apr))
ORDER BY empid;
+-------+-------------+-------+-------+
| 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;
+-------------+-------+-------+
| 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 |
+-------------+-------+-------+