Mastering MaxCompute PIVOT/UNPIVOT: Transform Rows and Columns Efficiently

MaxCompute (formerly ODPS) is Alibaba Cloud’s distributed big‑data processing platform, widely used internally and continuously improving performance and SQL usability.

The platform now supports a new generation SQL engine (MaxCompute2.0) with enhanced compiler error and warning handling, basic data types, complex types, CTE, VALUES, SEMIJOIN, SELECT TRANSFORM, User Defined Types, GROUPING SET, CUBE, ROLLUP, and dynamic type functions. Links to the series articles are listed.

PIVOT Overview

PIVOT rotates specified rows into multiple columns by aggregating values. It is part of the FROM clause.

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

<aggregate_function> : aggregation used for row‑to‑column conversion; cannot be nested and must reference columns from the upstream table.

<alias> : alias for the aggregated column.

<column> : column name used for rotation, not an expression.

<value> : value(s) corresponding to the column; can be expressions without aggregates or window functions.

<new_column> : name of the generated column; if omitted the system generates one.

Example data preparation and PIVOT queries demonstrate how to pivot sales counts, combine multiple aggregates, and filter specific shop‑year combinations.

create table shops_table as select * from (select * from values
('pen', 10, 500, 'shop1', 2020),
('pen', 11, 500, 'shop2', 2020),
('pen', 9, 300, 'shop3', 2020),
('pen', 12, 400, 'shop4', 2020),
('pen', 15, 200, 'shop1', 2021),
('pen', 16, 300, 'shop2', 2021),
('pen', 16, 400, 'shop3', 2021),
('pen', 15, 300, 'shop4', 2021),
('ruler', 20, 700, 'shop1', 2020),
('ruler', 19, 900, 'shop2', 2020),
('ruler', 22, 800, 'shop3', 2020),
('ruler', 19, 700, 'shop4', 2020),
('ruler', 25, 300, 'shop1', 2021),
('ruler', 20, 500, 'shop2', 2021),
('ruler', 23, 500, 'shop3', 2021),
('ruler', 26, 600, 'shop4', 2021)
) shops(item_name, count, sales, shop_name, year);

Without PIVOT, the transformation requires GROUP BY with CASE statements. With PIVOT, the same result is obtained concisely:

select * from (select item_name, year, count, shop_name from shops_table)
pivot (sum(count) for shop_name in ('shop1','shop2','shop3','shop4'));

Additional examples show how to alias aggregated columns, compute multiple aggregates (SUM, MAX), and restrict to specific shop‑year pairs.

UNPIVOT Overview

UNPIVOT rotates columns into rows and is also part of the FROM clause.

SELECT ...
FROM ...
UNPIVOT [EXCLUDE NULLS] (
    <new_column_of_name> [, <new_column_of_name> ...]
    FOR (<new_column_of_value> [, <new_column_of_value> ...])
    IN (
        (<column> [, <column> ...]) AS (<column_value> [, <column_value> ...]),
        ...
    )
) [...]

EXCLUDE NULLS : removes rows where all new columns are NULL.

<new_column_of_name> : column storing original column names.

<new_column_of_value> : column storing original values.

<column> : original columns to be unpivoted.

<column_value> : alias for the original column values.

UNPIVOT can be expressed as a CROSS JOIN with CASE WHEN logic. Example data and queries illustrate converting shop sales columns into a unified count column with a shop_name identifier, as well as grouping multiple columns into composite keys (e.g., east vs. west shops).

select * from shops
unpivot (count for shop_name in (shop1, shop2, shop3, shop4));

Both PIVOT and UNPIVOT provide concise, easy‑to‑use mechanisms for row‑to‑column and column‑to‑row transformations, simplifying query statements and improving developer productivity in large‑scale data processing.