PIVOT 子句PIVOT clause

用于数据透视。Used for data perspective. 可以基于特定列值获取聚合值,这些值将转换为在 SELECT 子句中使用的多个列。You can get the aggregated values based on specific column values, which are turned into multiple columns used in SELECT clause. 在表名或子查询之后指定 PIVOT 子句。You specify the PIVOT clause after the table name or subquery.

语法Syntax

PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
    FOR column_list IN ( expression_list ) )

参数Parameters

  • aggregate_expressionaggregate_expression

    聚合表达式(SUM(a)、COUNT(DISTINCT b) 等)。An aggregate expression (SUM(a), COUNT(DISTINCT b), etc.).

  • aggregate_expression_aliasaggregate_expression_alias

    聚合表达式的别名。An alias for the aggregate expression.

  • column_listcolumn_list

    FROM 子句中包含要替换为新列的列。Contains columns in the FROM clause, which The columns we want to replace with new columns. 我们可以使用括号括住列,例如 (c1, c2)We can use brackets to surround the columns, such as (c1, c2).

  • expression_listexpression_list

    指定新列,这些新列用于匹配 column_list 中的值(作为聚合条件)。Specifies new columns, which are used to match values in column_list as the aggregating condition. 我们也可以为其添加别名。We can also add aliases for them.

示例Examples

CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
INSERT INTO person VALUES
    (100, 'John', 30, 1, 'Street 1'),
    (200, 'Mary', NULL, 1, 'Street 2'),
    (300, 'Mike', 80, 3, 'Street 3'),
    (400, 'Dan', 50, 4, 'Street 4');

SELECT * FROM person
    PIVOT (
        SUM(age) AS a, AVG(class) AS c
        FOR name IN ('John' AS john, 'Mike' AS mike)
    );
+------+-----------+---------+---------+---------+---------+
|  id  |  address  | john_a  | john_c  | mike_a  | mike_c  |
+------+-----------+---------+---------+---------+---------+
| 200  | Street 2  | NULL    | NULL    | NULL    | NULL    |
| 100  | Street 1  | 30      | 1.0     | NULL    | NULL    |
| 300  | Street 3  | NULL    | NULL    | 80      | 3.0     |
| 400  | Street 4  | NULL    | NULL    | NULL    | NULL    |
+------+-----------+---------+---------+---------+---------+

SELECT * FROM person
    PIVOT (
        SUM(age) AS a, AVG(class) AS c
        FOR (name, age) IN (('John', 30) AS c1, ('Mike', 40) AS c2)
    );
+------+-----------+-------+-------+-------+-------+
|  id  |  address  | c1_a  | c1_c  | c2_a  | c2_c  |
+------+-----------+-------+-------+-------+-------+
| 200  | Street 2  | NULL  | NULL  | NULL  | NULL  |
| 100  | Street 1  | 30    | 1.0   | NULL  | NULL  |
| 300  | Street 3  | NULL  | NULL  | NULL  | NULL  |
| 400  | Street 4  | NULL  | NULL  | NULL  | NULL  |
+------+-----------+-------+-------+-------+-------+