UNPIVOT 语句允许多列堆叠成更少的列。 在基本情况下，多列被堆叠成两列：一个 NAME 列（包含源列的名称）和一个 VALUE 列（包含源列的值）。

DuckDB 实现了 SQL 标准的 UNPIVOT 语法和简化的 UNPIVOT 语法。 两者都可以利用 COLUMNS 表达式 自动检测要解透视的列。 PIVOT_LONGER 也可以用来代替 UNPIVOT 关键字。

有关UNPIVOT语句如何实现的详细信息，请参阅Pivot Internals 网站。

PIVOT 语句 是 UNPIVOT 语句的逆操作。

简化的 UNPIVOT 语法

完整的语法图如下，但简化的UNPIVOT语法可以使用电子表格数据透视表的命名约定总结为：

UNPIVOT ⟨dataset⟩
ON ⟨column(s)⟩
INTO
    NAME ⟨name-column-name⟩
    VALUE ⟨value-column-name(s)⟩
ORDER BY ⟨column(s)-with-order-direction(s)⟩
LIMIT ⟨number-of-rows⟩;

示例数据

所有示例都使用以下查询生成的数据集：

CREATE OR REPLACE TABLE monthly_sales
    (empid INTEGER, dept TEXT, Jan INTEGER, Feb INTEGER, Mar INTEGER, Apr INTEGER, May INTEGER, Jun INTEGER);
INSERT INTO monthly_sales VALUES
    (1, 'electronics', 1, 2, 3, 4, 5, 6),
    (2, 'clothes', 10, 20, 30, 40, 50, 60),
    (3, 'cars', 100, 200, 300, 400, 500, 600);

FROM monthly_sales;

UNPIVOT 手动

最典型的UNPIVOT转换是将已经透视的数据重新堆叠成每列一个名称和一个值。 在这种情况下，所有月份将被堆叠到一个month列和一个sales列中。

UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
    NAME month
    VALUE sales;

UNPIVOT 动态使用列表达式

在许多情况下，要取消透视的列数不容易预先确定。 在这个数据集的例子中，每次添加新月份时，上面的查询都必须更改。 COLUMNS 表达式 可以用来选择所有不是 empid 或 dept 的列。 这使得动态取消透视成为可能，无论添加了多少个月份，它都能正常工作。 下面的查询返回与上面相同的结果。

UNPIVOT monthly_sales
ON COLUMNS(* EXCLUDE (empid, dept))
INTO
    NAME month
    VALUE sales;

UNPIVOT 转换为多值列

UNPIVOT 语句具有额外的灵活性：支持超过2个目标列。 当目标是减少数据集的透视程度，但不完全堆叠所有透视列时，这可能很有用。 为了演示这一点，下面的查询将生成一个数据集，其中每个季度内的月份数（第1、2或3个月）有一个单独的列，每个季度有一个单独的行。 由于季度比月份少，这确实使数据集变长，但没有上面那么长。

为了实现这一点，ON 子句中包含了多组列。 q1 和 q2 别名是可选的。 ON 子句中每组列的列数必须与 VALUE 子句中的列数匹配。

UNPIVOT monthly_sales
    ON (jan, feb, mar) AS q1, (apr, may, jun) AS q2
    INTO
        NAME quarter
        VALUE month_1_sales, month_2_sales, month_3_sales;

在 SELECT 语句中使用 UNPIVOT

UNPIVOT 语句可以包含在 SELECT 语句中作为 CTE（公共表表达式或 WITH 子句），或者作为子查询。 这使得 UNPIVOT 可以与其他 SQL 逻辑一起使用，并且可以在一个查询中使用多个 UNPIVOT。

在CTE中不需要SELECT，可以将UNPIVOT关键字视为替代它。

WITH unpivot_alias AS (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
)
SELECT * FROM unpivot_alias;

一个UNPIVOT可以在子查询中使用，并且必须用括号括起来。 请注意，这种行为与SQL标准的Unpivot不同，如后续示例所示。

SELECT *
FROM (
    UNPIVOT monthly_sales
    ON COLUMNS(* EXCLUDE (empid, dept))
    INTO
        NAME month
        VALUE sales
) unpivot_alias;

在UNPIVOT语句中的表达式

DuckDB 允许在 UNPIVOT 语句中使用表达式，前提是这些表达式仅涉及单个列。这些表达式可用于执行计算以及 显式类型转换。例如：

UNPIVOT
    (SELECT 42 AS col1, 'woot' AS col2)
    ON
        (col1 * 2)::VARCHAR,
        col2;

简化的 UNPIVOT 完整语法图

以下是UNPIVOT语句的完整语法图。

SQL标准 UNPIVOT 语法

完整的语法图如下，但SQL标准的UNPIVOT语法可以总结为：

FROM [dataset]
UNPIVOT [INCLUDE NULLS] (
    [value-column-name(s)]
    FOR [name-column-name] IN [column(s)]
);

请注意，name-column-name表达式中只能包含一个列。

SQL标准 UNPIVOT 手动操作

要使用SQL标准语法完成基本的UNPIVOT操作，只需要进行一些简单的添加。

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (jan, feb, mar, apr, may, jun)
);

SQL 标准 UNPIVOT 动态使用 COLUMNS 表达式

COLUMNS 表达式 可以用于动态确定 IN 列表中的列。 即使向数据集中添加了额外的 month 列，它仍然可以继续工作。 它产生与上述查询相同的结果。

FROM monthly_sales UNPIVOT (
    sales
    FOR month IN (columns(* EXCLUDE (empid, dept)))
);

SQL标准 UNPIVOT 转换为多值列

The UNPIVOT statement has additional flexibility: more than 2 destination columns are supported. This can be useful when the goal is to reduce the extent to which a dataset is pivoted, but not completely stack all pivoted columns. To demonstrate this, the query below will generate a dataset with a separate column for the number of each month within the quarter (month 1, 2, or 3), and a separate row for each quarter. Since there are fewer quarters than months, this does make the dataset longer, but not as long as the above.

为了实现这一点，UNPIVOT语句的value-column-name部分包含了多个列。 IN子句中包含了多组列。 q1和q2别名是可选的。 IN子句中每组列的列数必须与value-column-name部分中的列数匹配。

FROM monthly_sales
UNPIVOT (
    (month_1_sales, month_2_sales, month_3_sales)
    FOR quarter IN (
        (jan, feb, mar) AS q1,
        (apr, may, jun) AS q2
    )
);

SQL标准 UNPIVOT 完整语法图

以下是SQL标准版本的UNPIVOT语句的完整语法图。