Usage and examples of the PIVOT and UNPIVOT keywords-MaxCompute(MaxCompute)-阿里云帮助中心

PIVOT transforms a narrow table (for example, product, season, sales_amount) into a wide table (for example, product, Q1, Q2, Q3, Q4) by aggregating rows into columns. UNPIVOT does the reverse — it turns wide columns back into rows, making it easier to filter and analyze data uniformly.

Both keywords go in the FROM clause and can be combined with JOIN and other clauses.

Note: PIVOT is in canary release and may not be available to all users.

PIVOT

Syntax

SELECT ...
FROM ...
PIVOT (
    <aggregate function> [AS <alias>] [, <aggregate function> [AS <alias>]] ...
    FOR (<column> [, <column>] ...)
    IN (
        (<value> [, <value>] ...) AS <new column>
        [, (<value> [, <value>] ...) AS <new column>]
        ...
       )
    )
[...]

Parameters

Parameter	Required	Description
`aggregate function`	Yes	The aggregate function used for row-to-column conversion. For supported functions, see Aggregate functions.
`alias`	No	An alias for the aggregate function. Included in the generated column name.
`column`	Yes	The source column whose values are aggregated.
`value`	Yes	The row value to pivot on, used as the column selector.
`new column`	No	The name of the output column after conversion.

Output columns and naming rules

The output table has two types of columns:

Grouping columns: source columns not referenced in the aggregate functions or the FOR clause. These are carried through unchanged.
Pivoted columns: one column per value in the IN list, named according to the alias rules below.

The aliases you define in PIVOT determine the names of the pivoted output columns:

No alias — PIVOT (agg1 for axis1 in ('1', '2', '3')): column names are the values themselves — '1', '2', '3'.
One aggregate alias — PIVOT (agg1 AS a for axis1 in ('1', '2', '3')): columns are named value_aggregateAlias — '1'_a, '2'_a, '3'_a.
Multiple aggregate aliases — PIVOT (agg1 AS a, agg2 AS b for axis1 in ('1', '2', '3')): each aggregate gets its own set of columns — '1'_a, '2'_a, '1'_b, '2'_b.
Expression values (some or all) — MaxCompute auto-generates aliases for any expression without an explicit alias, and for any aggregate function without an alias. The query is internally rewritten with generated aliases, and columns are named expressionAlias_aggregateAlias.

Limitations

An aggregate function cannot be nested inside another function.
Aggregate functions can include scalar functions and grouping columns, but cannot contain window functions or other aggregate functions.
Columns inside an aggregate function must come from the source table being pivoted.
An alias can only appear as part of the generated column name — it cannot be an expression.
A value expression can reference only source table columns and can contain scalar functions, but not aggregate functions or window functions.

How PIVOT works

PIVOT is equivalent to GROUP BY with aggregate functions and FILTER clauses. The following statements produce the same result:

-- Using PIVOT
SELECT ...
FROM ...
PIVOT (
  agg1 AS a, agg2 AS b, ...
  FOR (axis1, ..., axisN)
  IN (
      (v11, ..., v1N) AS label1,
      (v21, ..., v2N) AS label2,
      ...)
)

-- Equivalent without PIVOT
SELECT
  k1, ..., kN,
  agg1 AS label1_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label1_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...,
  agg1 AS label2_a FILTER (WHERE axis1 = v11 AND ... AND axisN = v1N),
  agg2 AS label2_b FILTER (WHERE axis1 = v21 AND ... AND axisN = v2N),
  ...
FROM source_table
GROUP BY k1, ..., kN

k1, ..., kN are the source columns not listed in the aggregate functions or the FOR clause.

Examples

The examples below use a fruit sales table with quarterly transaction data:

-- Create and populate the table
CREATE TABLE mf_cop_sales (
  tran_id   BIGINT,
  productID STRING,
  tran_amt  DECIMAL,
  season    STRING
);

INSERT INTO TABLE mf_cop_sales VALUES
  (1,  'apple',  100, 'Q1'),
  (2,  'orange', 200, 'Q1'),
  (3,  'banana', 300, 'Q1'),
  (4,  'apple',  400, 'Q2'),
  (5,  'orange', 500, 'Q2'),
  (6,  'banana', 600, 'Q2'),
  (7,  'apple',  700, 'Q3'),
  (8,  'orange', 800, 'Q3'),
  (9,  'banana', 700, 'Q3'),
  (10, 'apple',  500, 'Q4'),
  (11, 'orange', 400, 'Q4'),
  (12, 'banana', 200, 'Q4');

Total sales by quarter

Pivot the season column into four named columns — one per quarter — and sum all transactions into each:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt)
  FOR season IN ('Q1' AS spring, 'Q2' AS summer, 'Q3' AS autumn, 'Q4' AS winter)
);

Result:

+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Total sales per product with an aggregate alias

Adding an alias to the aggregate function (AS sumbypro) appends that alias to each output column name, producing 'value'_sumbypro:

SELECT *
FROM (SELECT productid, tran_amt FROM mf_cop_sales)
PIVOT (
  SUM(tran_amt) AS sumbypro
  FOR productid IN ('apple', 'orange', 'banana')
);

Result:

+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Maximum sale in Q4

Use MAX to find the highest single transaction amount in Q4:

SELECT *
FROM (SELECT season, tran_amt FROM mf_cop_sales)
PIVOT (MAX(tran_amt) FOR season IN ('Q4'));

Result:

+------+
| 'Q4' |
+------+
| 500  |
+------+

UNPIVOT

Syntax

SELECT ...
FROM ...
UNPIVOT (
  <new column of value> [, <new column of value>] ...
  FOR (<new column of name> [, <new column of name>] ...)
  IN (
      (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]
      [, (<column> [, <column>] ...) [AS (<column value> [, <column value>] ...)]]
      ...
    )
)
[...]

Parameters

Parameter	Required	Description
`new column of value`	Yes	The name of the new column that receives the values from the source columns being unpivoted.
`new column of name`	Yes	The name of the new column that receives the original column names (or their aliases).
`column`	Yes	The source column to unpivot. Its name populates `new column of name`; its values populate `new column of value`.
`column value`	No	An alias for the source column. Replaces the column name in the `new column of name` output.

Limitations

The number of new column of value parameters must equal the number of column groups in IN (...).
The number of new column of name parameters must equal the number of alias groups in IN (...).
new column of value and new column of name must be unique column names and cannot contain expressions.
column value can be a constant or an expression, but the expression cannot reference column names — it must evaluate to a constant.
Each column group in IN (...) can contain up to 100 columns. More than 100 columns causes data bloat.
Add EXCLUDE NULLS after the UNPIVOT keyword to drop rows where any unpivoted column is NULL.

Auto-alias rules

If you omit the AS (...) clause for a column group, MaxCompute generates aliases automatically:

UNPIVOT (measure1 FOR axis IN (c1, c2, c3)) is rewritten internally as UNPIVOT (measure1 FOR axis IN (c1 AS c1, c2 AS c2, c3 AS c3)).
In other cases, MaxCompute generates STRING aliases for unspecified columns.
If you specify aliases for only some columns, the explicit aliases must be of the STRING type to be compatible with the auto-generated ones. If any alias is not a STRING, specify aliases for all columns.

How UNPIVOT works

UNPIVOT is equivalent to a CROSS JOIN with a CASE WHEN expression:

-- Using UNPIVOT
SELECT ...
FROM ...
UNPIVOT (
    (measure1, ..., measureM)
    FOR (axis1, ..., axisN)
    IN ((c11, ..., c1M) AS (value11, ..., value1N),
        (c21, ..., c2M) AS (value21, ..., value2N), ...))
[...]

-- Equivalent without UNPIVOT
SELECT
    k1, ..., kN,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c11
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c21
      ...
      ELSE NULL
    END AS measure1,
    ...,
    CASE
      WHEN axis1 = value11 AND ... AND axisN = value1N THEN c1M
      WHEN axis1 = value21 AND ... AND axisN = value2N THEN c2M
      ELSE NULL
    END AS measureM,
    axis1, ..., axisN
FROM source_table
JOIN (VALUES (value11, ..., value1N), (value21, ..., value2N), ...)
  AS generated_table_name(axis1, ..., axisN)

Examples

The examples below use a shop sales table with per-product, per-year data across four shops:

-- Create and populate the table
CREATE TABLE mf_shops (
  item_id BIGINT,
  year    STRING,
  shop1   DECIMAL,
  shop2   DECIMAL,
  shop3   DECIMAL,
  shop4   DECIMAL
);

WITH shops_table AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
INSERT OVERWRITE TABLE mf_shops SELECT * FROM shops_table;

Consolidate shop columns into rows

Turn the four shop columns into rows, with a sales column for the amount and a shop column for the shop name. MaxCompute auto-generates the shop name aliases from the source column names:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (shop1, shop2, shop3, shop4));

Result:

+------------+------------+------------+-------+
| item_id    | year       | sales      | shop  |
+------------+------------+------------+-------+
| 1          | 2020       | 100        | shop1 |
| 1          | 2020       | 200        | shop2 |
| 1          | 2020       | 300        | shop3 |
| 1          | 2020       | 400        | shop4 |
| 1          | 2021       | 100        | shop1 |
| 1          | 2021       | 200        | shop2 |
| 1          | 2021       | 200        | shop3 |
| 1          | 2021       | 100        | shop4 |
| 2          | 2020       | 300        | shop1 |
| 2          | 2020       | 400        | shop2 |
| 2          | 2020       | 300        | shop3 |
| 2          | 2020       | 200        | shop4 |
| 2          | 2021       | 400        | shop1 |
| 2          | 2021       | 300        | shop2 |
| 2          | 2021       | 100        | shop3 |
| 2          | 2021       | 100        | shop4 |
+------------+------------+------------+-------+

Use custom aliases for shop names

Replace the source column names in the output with descriptive string labels:

SELECT * FROM mf_shops
UNPIVOT (sales FOR shop IN (
  shop1 AS 'shop_name_1',
  shop2 AS 'shop_name_2',
  shop3 AS 'shop_name_3',
  shop4 AS 'shop_name_4'
));

Result:

+------------+------------+------------+-------------+
| item_id    | year       | sales      | shop        |
+------------+------------+------------+-------------+
| 1          | 2020       | 100        | shop_name_1 |
| 1          | 2020       | 200        | shop_name_2 |
| 1          | 2020       | 300        | shop_name_3 |
| 1          | 2020       | 400        | shop_name_4 |
| 1          | 2021       | 100        | shop_name_1 |
| 1          | 2021       | 200        | shop_name_2 |
| 1          | 2021       | 200        | shop_name_3 |
| 1          | 2021       | 100        | shop_name_4 |
| 2          | 2020       | 300        | shop_name_1 |
| 2          | 2020       | 400        | shop_name_2 |
| 2          | 2020       | 300        | shop_name_3 |
| 2          | 2020       | 200        | shop_name_4 |
| 2          | 2021       | 400        | shop_name_1 |
| 2          | 2021       | 300        | shop_name_2 |
| 2          | 2021       | 100        | shop_name_3 |
| 2          | 2021       | 100        | shop_name_4 |
+------------+------------+------------+-------------+

Group columns before unpivoting

Pair shops into east and west groups, unpivoting each pair into two value columns (sales1, sales2):

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR shop IN (
    (shop1, shop2) AS 'east_shop',
    (shop3, shop4) AS 'west_shop'
  )
);

Result:

+------------+------------+------------+------------+-----------+
| item_id    | year       | sales1     | sales2     | shop      |
+------------+------------+------------+------------+-----------+
| 1          | 2020       | 100        | 200        | east_shop |
| 1          | 2020       | 300        | 400        | west_shop |
| 1          | 2021       | 100        | 200        | east_shop |
| 1          | 2021       | 200        | 100        | west_shop |
| 2          | 2020       | 300        | 400        | east_shop |
| 2          | 2020       | 300        | 200        | west_shop |
| 2          | 2021       | 400        | 300        | east_shop |
| 2          | 2021       | 100        | 100        | west_shop |
+------------+------------+------------+------------+-----------+

Use multiple name columns

Add a second FOR column to capture an additional label dimension. In this example, each shop group gets both a name and a geographic location:

SELECT * FROM mf_shops
UNPIVOT (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Result:

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+

Exclude null rows with EXCLUDE NULLS

When a source row has NULL in any unpivoted column, use EXCLUDE NULLS to drop it from the output. In the example below, item_id=3 has all-null shop values and is excluded:

WITH shops AS (
  SELECT * FROM VALUES
    (1, 2020, 100, 200, 300, 400),
    (1, 2021, 100, 200, 200, 100),
    (2, 2020, 300, 400, 300, 200),
    (2, 2021, 400, 300, 100, 100),
    (3, 2020, NULL, NULL, NULL, NULL)
    shops(item_id, year, shop1, shop2, shop3, shop4)
)
SELECT * FROM shops
UNPIVOT EXCLUDE NULLS (
  (sales1, sales2) FOR (shop_name, location) IN (
    (shop1, shop2) AS ('east_shop', 'east'),
    (shop3, shop4) AS ('west_shop', 'west')
  )
);

Result (the item_id=3 rows are absent):

+------------+------------+------------+------------+-----------+----------+
| item_id    | year       | sales1     | sales2     | shop_name | location |
+------------+------------+------------+------------+-----------+----------+
| 1          | 2020       | 100        | 200        | east_shop | east     |
| 1          | 2020       | 300        | 400        | west_shop | west     |
| 1          | 2021       | 100        | 200        | east_shop | east     |
| 1          | 2021       | 200        | 100        | west_shop | west     |
| 2          | 2020       | 300        | 400        | east_shop | east     |
| 2          | 2020       | 300        | 200        | west_shop | west     |
| 2          | 2021       | 400        | 300        | east_shop | east     |
| 2          | 2021       | 100        | 100        | west_shop | west     |
+------------+------------+------------+------------+-----------+----------+