PIVOT-KlauselPIVOT clause

Wird für Daten Perspektive verwendet.Used for data perspective. Sie können die aggregierten Werte basierend auf bestimmten Spaltenwerten, die in mehrere in der-Klausel verwendete Spalten umgewandelt werden, erhalten SELECT .You can get the aggregated values based on specific column values, which are turned into multiple columns used in SELECT clause. Die- PIVOT Klausel wird nach dem Tabellennamen oder der Unterabfrage angegeben.You specify the PIVOT clause after the table name or subquery.

SyntaxSyntax

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

ParameterParameters

  • aggregate_expressionaggregate_expression

    Ein Aggregat Ausdruck (Sum (a), count (verschieden b) usw.).An aggregate expression (SUM(a), COUNT(DISTINCT b), etc.).

  • aggregate_expression_aliasaggregate_expression_alias

    Ein Alias für den Aggregat Ausdruck.An alias for the aggregate expression.

  • column_listcolumn_list

    Enthält Spalten in der- FROM Klausel, in der die Spalten durch neue Spalten ersetzt werden sollen.Contains columns in the FROM clause, which The columns we want to replace with new columns. Wir können eckige Klammern zum Umschließen der Spalten verwenden, z (c1, c2) . b..We can use brackets to surround the columns, such as (c1, c2).

  • expression_listexpression_list

    Gibt neue Spalten an, die verwendet werden, um Werte in column_list als Aggregations Bedingung abzugleichen.Specifies new columns, which are used to match values in column_list as the aggregating condition. Wir können auch Aliase für diese hinzufügen.We can also add aliases for them.

BeispieleExamples

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