Categories:

Query Syntax

UNPIVOT¶

Rotates a table by transforming columns into rows. UNPIVOT is a relational operator that accepts two columns (from a table or subquery), along with a list of columns, and generates a row for each column specified in the list. In a query, it is specified in the FROM clause after the table name or subquery.

Note that UNPIVOT is not exactly the reverse of PIVOT as it cannot undo aggregations made by PIVOT.

This operator can be used to transform a wide table (e.g. empid, jan_sales, feb_sales, mar_sales) into a narrower table (e.g. empid, month, sales).

See also:

PIVOT

Syntax¶

SELECT ...
FROM ...
   UNPIVOT ( <value_column>
             FOR <name_column> IN ( <column_list> ) )

[ ... ]
Copy
value_column

The name to assign to the generated column that will be populated with the values from the columns in the column list.

name_column

The name to assign to the generated column that will be populated with the names of the columns in the column list.

column_list

The names of the columns in the source table or subequery that will be narrowed into a single pivot column. The column names will populate name_column, and the column values will populate value_column.

The column_list should contain only literal column names, not a subquery.

Examples¶

Given a table, monthly_sales, with the following structure and data, unpivot the individual month columns to return a single sales value by month for each employee:

-- example setup
CREATE OR REPLACE TABLE monthly_sales(empid INT, dept TEXT, jan INT, feb INT, mar INT, april INT);

INSERT INTO monthly_sales VALUES
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);

-- UNPIVOT example
SELECT * FROM monthly_sales
    UNPIVOT(sales FOR month IN (jan, feb, mar, april))
    ORDER BY empid;

+-------+-------------+-------+-------+
| EMPID | DEPT        | MONTH | SALES |
|-------+-------------+-------+-------|
|     1 | electronics | JAN   |   100 |
|     1 | electronics | FEB   |   200 |
|     1 | electronics | MAR   |   300 |
|     1 | electronics | APRIL |   100 |
|     2 | clothes     | JAN   |   100 |
|     2 | clothes     | FEB   |   300 |
|     2 | clothes     | MAR   |   150 |
|     2 | clothes     | APRIL |   200 |
|     3 | cars        | JAN   |   200 |
|     3 | cars        | FEB   |   400 |
|     3 | cars        | MAR   |   100 |
|     3 | cars        | APRIL |    50 |
+-------+-------------+-------+-------+
Copy
Language: English