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;

使用 Columns 表达式动态 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 语句可以作为 CTE（公共表表达式，或 WITH 子句）或子查询包含在 SELECT 语句中。这允许 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 标准使用 COLUMNS 表达式动态 UNPIVOT

COLUMNS 表达式可用于动态确定 IN 列列表。即使将额外的 month 列添加到数据集中，此功能也将继续有效。它产生与上述查询相同的结果。

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

SQL 标准 UNPIVOT 到多个值列

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

为了实现这一点，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 语句的完整语法图。