Usage and examples of the PIVOT and UNPIVOT keywords - MaxCompute

MaxCompute supports the PIVOT and UNPIVOT keywords. You can use the PIVOT keyword to transform one or more rows into columns based on an aggregation. You can use the UNPIVOT keyword to transform one or more columns into rows. This topic describes how to use the PIVOT and UNPIVOT keywords and provides examples.

PIVOT keyword

The PIVOT keyword generates a column for each specified group of row values. PIVOT is part of the FROM clause and can be used with other keywords, such as JOIN.

Note

The PIVOT keyword is in phased release. Some users may not have access to this feature.

Command format

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	An aggregate function. For more information, see Overview of aggregate functions.
alias	No	The alias for the aggregate function. The alias is related to the column names generated after the PIVOT operation. For more information, see Limits.
column	Yes	The name of the column in the source table whose row values you want to transform into columns.
value	Yes	The row values to transform into columns.
new column	No	The name of the new column after the transformation.

Limits

Aggregate functions:
- Aggregate functions cannot be nested within any other functions.
- The parameters of an aggregate function can be an expression that consists of scalar functions and columns.
- The parameters of an aggregate function cannot contain other aggregate functions or window functions.
- The columns in an aggregate function must be from the upstream table.
alias must be a column name and cannot be an expression.
The value can be an expression. The columns in the expression must be from the upstream table. The expression can contain scalar functions but cannot contain any aggregate functions or window functions.
The aliases used in a PIVOT operation are important because they determine the names of the generated columns. The naming conventions are as follows:
- PIVOT (agg1 for axis1 in ('1', '2', '3', ...)):
  If value is a constant instead of an expression and the aggregate function does not have an alias, the generated column names are the values themselves, such as '1', `'2'`, and `'3'`.
- PIVOT (agg1 as a for axis1 in ('1', '2', '3', ...)):
  If value is a constant and the aggregate function has an alias, the generated column names are in the format value_AggregateFunctionAlias, such as '1'_a and `'2'_a`.
  If you run the SET odps.sql.bigquery.compatible=true; command to enable BigQuery compatibility mode, the generated column names are in the format AggregateFunctionAlias_value, such as a_'1' and `a_'2'`.
- PIVOT (agg1 as a, agg2 as b for axis1 in ('1', '2', '3', ...)):
  If value is a constant and multiple aggregate functions have aliases, the generated column names are in the format value_AggregateFunctionAlias, such as '1'_a, '2'_a, ..., '1'_b, '2'_b, ....
  If you run the SET odps.sql.bigquery.compatible=true; command to enable BigQuery compatibility mode, the generated column names are in the format AggregateFunctionAlias_value, such as a_'1', a_'2', ..., b_'1', b_'2', ....
- PIVOT (agg1 as a, agg2 for axis1 in (expr1, expr2, '3', ...)):
  If a value is an expression, MaxCompute first generates aliases for the expressions, such as expr1 and expr2, and for any aggregate function without an alias, such as agg2. The statement is interpreted as PIVOT (agg1 as a, agg2 as generated_alias1 for axis1 in (expr1 as generated_alias2, expr2 as generated_alias3, '3', ...)). The generated column names are in the format ValueAlias_AggregateFunctionAlias, such as generated_alias2_a, generated_alias3_a, '3'_a, ..., generated_alias2_generated_alias1, generated_alias3_generated_alias1, '3'_generated_alias1, ....
  If you run the SET odps.sql.bigquery.compatible=true; command to enable BigQuery compatibility mode, the generated column names are in the format AggregateFunctionAlias_ValueAlias, such as a_generated_alias2, a_generated_alias3, a_'3', ..., generated_alias1_generated_alias2, generated_alias1_generated_alias3, generated_alias1_'3', ....

Usage notes

The PIVOT syntax is equivalent to a combination of `GROUP BY`, an aggregate function, and `FILTER`. For example:

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

This is equivalent to the following statement:

select 
k1, ... kN, 
agg1 AS label1_a filter (where axis1 = v11 and ... and axisN = v1N), 
agg2 AS label1_b filter (where axis1 = v11 and ... and axisN = v1N), 
..., 
agg1 AS label2_a filter (where axis1 = v21 and ... and axisN = v2N),
agg2 AS label2_b filter (where axis1 = v21 and ... and axisN = v2N), 
..., 
from xxxxxx
group by k1, ... kN

In this statement, the table in the FROM clause is the result of the upstream PIVOT operation. k1, ... kN is the set of all columns that do not appear in agg1, agg2, ... or axis1, ..., axisN.

Examples

The sample data shows a company's fruit sales per season. The Data Definition Language (DDL) statement to create the table is as follows.

-- Create a 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');
-- The details of the sales table are as follows
select * from mf_cop_sales;
+------------+------------+------------+------------+
| tran_id    | productid  | tran_amt   | season     | 
+------------+------------+------------+------------+
| 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         | 
+------------+------------+------------+------------+

Query the sales for each season of the year.

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))
;
-- The following result is returned:
+--------+--------+--------+--------+
| spring | summer | autumn | winter |
+--------+--------+--------+--------+
| 600    | 1500   | 2200   | 1100   |
+--------+--------+--------+--------+

Query the sales for each product of the year.

SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| 'apple'_sumbypro | 'orange'_sumbypro | 'banana'_sumbypro |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

-- Enable BigQuery compatibility mode
SET odps.sql.bigquery.compatible=true;
SELECT  *
FROM    (
            SELECT  productid
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (SUM(tran_amt)
AS sumbypro FOR productid IN ('apple','orange','banana'))
;
-- The following result is returned:
+------------------+-------------------+-------------------+
| sumbypro_'apple' | sumbypro_'orange' | sumbypro_'banana' |
+------------------+-------------------+-------------------+
| 1700             | 1900              | 1800              |
+------------------+-------------------+-------------------+

Query the product with the highest sales in the fourth quarter (Q4).

SELECT  *
FROM    (
            SELECT  season
                    ,tran_amt
            FROM    mf_cop_sales
        ) 
PIVOT (MAX(tran_amt) FOR season IN ('Q4'))
;
-- The following result is returned:
+------+
| 'q4' |
+------+
| 500  |
+------+

UNPIVOT keyword

The UNPIVOT keyword transforms columns into rows. UNPIVOT is part of the FROM clause and can be used with other keywords, such as JOIN.

Command format

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>] ...)]]
      ...
    )
)
[...]

The parameters are described as follows:

Parameter	Required	Description
new column of value	Yes	The name of the new column generated after the transformation. The values in this column are populated from the values of the columns that are transformed into rows.
new column of name	Yes	The name of the new column generated after the transformation. The values in this column are populated from the names of the columns that are transformed into rows.
column	Yes	The names of the columns to transform into rows. The column names are used to populate new column of name. The column values are used to populate new column of value.
column value	No	The alias for the columns that are transformed into rows.

Limits

Each new value column (new column of value) corresponds to a group of columns specified for transformation ((<column1> [, <column2>] ...)). Therefore, the number of new column of values must match the number of column groups. For example, new column of value1, ..., new column of valueM corresponds to:
```
(column11, ..., column1N) AS (column value11, ..., column value1N),
(column21, ..., column2N) AS (column value21, ..., column value2N),
...
(columnM1, ..., columnMN) AS (column valueM1, ..., column valueMN)
```
Each new name column (new column of name) corresponds to a group of column aliases ((<column value1> [, <column value2>] ...)). Therefore, the number of new column of names must match the number of alias groups. For example, new column of name1, ..., new column of nameM corresponds to:
```
(column value11, ..., column value1N), 
(column value21, ..., column value2N), 
...
(column valueM1, ..., column valueMN)
```
Note
You can omit (<column value> [, <column value>] ...). MaxCompute automatically generates aliases for the specified columns. If you want to customize the aliases, make sure that the number of aliases matches the number of columns.
The new column of value and new column of name collections must contain only column names and no expressions. Additionally, the new column of value and new column of name collections cannot contain duplicate names because the elements within the new column of value and new column of name collections are output as columns.
column must be a column name from the upstream table.
column value can be a constant or an expression. If it is an expression, it cannot contain any columns. This ensures that it can be reduced to a constant through constant folding.
The number of column groups (<column1> [, <column2>] ...) cannot exceed 100. Otherwise, excessive data expansion may occur.
If you omit the column aliases ((<column value> [, <column value>] ...)), MaxCompute automatically generates a set of string values to replace them. The rules are as follows:
- For UNPIVOT (measure1 for axis in (c1, c2, c3, ...)), the generated values are (c1, c2, c3, ...). The original syntax is rewritten as: UNPIVOT (measure1 for axis in (c1 as c1, c2 as c2, c3 as c3, ...)).
- In all other cases where aliases are not specified, MaxCompute automatically generates aliases for the specified columns.
- If you omit aliases for some columns but specify aliases for others, make sure that the specified aliases are of the STRING type. This ensures compatibility with the automatically generated STRING aliases. If you use a non-STRING type, you must specify all aliases and cannot omit any.

Usage notes

The UNPIVOT syntax is equivalent to a combination of `CROSS JOIN` and `FILTER` (a `CASE WHEN` expression). For example:

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

This is equivalent to the following statement:

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 xxxx 
    join (values (value11, ..., value1N),(value21, ..., value2N), ...) as generated_table_name(axis1, ..., axisN))

Examples

The sample data shows the sales of items in different stores for specific years. The DDL statement to create the table is as follows.

-- Create a table 
create table mf_shops(item_id bigint, 
                      year string, 
                      shop1 decimal, 
                      shop2 decimal, 
                      shop3 decimal, 
                      shop4 decimal); 
-- Insert data
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; 
-- Query data
select * from mf_shops;
-- The following result is returned:
+------------+------+-------+-------+-------+-------+
| item_id    | year | shop1 | shop2 | shop3 | shop4 |
+------------+------+-------+-------+-------+-------+
| 1          | 2020 | 100   | 200   | 300   | 400   |
| 1          | 2021 | 100   | 200   | 200   | 100   |
| 2          | 2020 | 300   | 400   | 300   | 200   |
| 2          | 2021 | 400   | 300   | 100   | 100   |
+------------+------+-------+-------+-------+-------+

Merge the sales figures from all stores and display them in a new column named sales.

-- Merge the sales figures from all stores.
select * from mf_shops
unpivot (sales for shop in (shop1, shop2, shop3, shop4));

-- The following result is returned:
+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------+

You can assign an alias to each store name. The alias can be a value from the table or a string.

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'));

-- The following result is returned:
+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------+

Assume that `shop1` and `shop2` are east-region stores, and `shop3` and `shop4` are west-region stores. The following query displays the sales for the east and west regions. The `sales1` and `sales2` columns store the sales figures for the two stores in each region, respectively.

select * from mf_shops
unpivot ((sales1, sales2) for shop in ((shop1, shop2) as 'east_shop', (shop3, shop4) as 'west_shop'));

-- The following result is returned:
+------------+------------+------------+------------+------+
| 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 |
+------------+------------+------------+------------+------+

You can use multiple columns for aliases. The number of value columns must also be increased to match.

select * from mf_shops
unpivot ((sales1, sales2) for (shop_name, location) in ((shop1, shop2) as ('east_shop', 'east'), (shop3, shop4) as ('west_shop', 'west')));

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

You can use `EXCLUDE NULLS` to filter out rows where `sales1` and `sales2` are null.

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')));

-- The following result is returned:
+------------+------------+------------+------------+-----------+----------+
| 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     |
+------------+------------+------------+------------+-----------+----------+