All Products
Search

This Product

    MaxCompute:PIVOT and UNPIVOT

    Document Center

    MaxCompute:PIVOT and UNPIVOT

    Last Updated:Mar 26, 2026

    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 are placed in the FROM clause and can be combined with JOIN and other clauses.

    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

    ParameterRequiredDescription
    aggregate functionYesThe aggregate function used for row-to-column conversion. For supported functions, see Aggregate functions.
    aliasNoAn alias for the aggregate function. Forms part of the generated column name.
    columnYesThe column in the source table whose values you want to aggregate.
    valueYesThe row value to pivot on (used as the column selector).
    new columnNoThe name of the resulting column after conversion.

    Column naming rules

    The aliases you define in PIVOT determine the names of the 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')): columns follow the same pattern for each aggregate — '1'_a, '2'_a, '1'_b, '2'_b.

    • Expression values (some or all) — MaxCompute auto-generates aliases for any expression that lacks an explicit alias, and for any aggregate function that lacks 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 grouped columns, but cannot contain window functions or other aggregate functions.

    • The columns referenced 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 columns in the source table that are not listed in the aggregate functions or the FOR clause.

    Examples

    The examples below use a fruit sales table with quarterly 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:

    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) changes the output column names to '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

    ParameterRequiredDescription
    new column of valueYesThe name of the new column that receives the values from the source columns being unpivoted.
    new column of nameYesThe name of the new column that receives the original column names (or their aliases).
    columnYesThe source column to unpivot. Its name populates new column of name; its values populate new column of value.
    column valueNoAn 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 exclude rows that contain null values in the unpivoted columns.

    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 manually specified 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:

    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 custom 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 groups (east and west) and unpack 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

    Specify multiple FOR columns to capture additional label dimensions (here, shop name and 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     |
+------------+------------+------------+------------+-----------+----------+