Unpivot
Unpivot is a transformation component that uses an SQL UNPIVOT function to transform columns into rows. Generally this means reducing the number of columns and increasing the number of rows.
The Unpivot component expects a number of 'Columns to Narrow' that contain data to be flattened into rows. The names of the 'Columns to Narrow' will become values in a new column specified by the 'Output Names Column Name' property and the values from the 'Columns to Narrow' will become values in a new column specified by the 'Output Values Column Name'.
The Unpivot component is not a strict reversal of the Pivot component, since UNPIVOT cannot undo aggregations made by PIVOT.
Properties
Name = string
A human-readable name for the component.
Columns to Narrow = dual listbox
Select the columns in the source table to narrow into a single pivot column. The names of these columns will populate one new output column, the values of these columns will populate another new output column. The names of these new columns are dictated by the 'Output Names Column Name' and 'Output Values Column Name' properties.
Output Names Column Name = string
Assign a name to the generated column that will be populated with the names from the columns in the column list.
Output Values Column Name = string
Assign a name to the generated column that will be populated with the values from the columns in the column list.
Examples
Unpivot the APPLES and ORANGES columns into a QUANTITY column.
We have a table of salespeople and the amounts of each product they've sold. We'd like to make our table structure a little more generic in case we want to add more products in later so we've decided to not have separate columns for each product type.
Input Data
+-------------+--------+---------+
| SALESPERSON | APPLES | ORANGES |
+-------------+--------+---------+
| John | 35 | 20 |
| Jane | 15 | 25 |
| Mike | 20 | 30 |
+-------------+--------+---------+
Let's unpivot the APPLES and ORANGES columns so they disappear and become entries in a PRODUCT column. The values from our APPLES and ORANGES columns can then become values in a QUANTITY column.
If you've seen the examples on the Pivot component documentation you will remember that we took a list of sales and pivoted the QUANTITY column to create the input data you see above.
We can now reverse that operation by unpivoting those same columns. However, it is not a perfect recreation of the original data since instead of John having two rows selling 30 and 5 apples, now he has one row selling 35. Thus we can see that aggregating data is not perfectly reversible and some information is lost along the way.
Unpivot component properties:
- Columns To Narrow:
- APPLES
- ORANGES
- Output Names Column Name: PRODUCT
- Output Values Column Name: QUANTITY
Output Data
+-------------+---------+----------+
| SALESPERSON | PRODUCT | QUANTITY |
+-------------+---------+----------+
| John | Apples | 35 |
| John | Oranges | 20 |
| Jane | Apples | 15 |
| Jane | Oranges | 25 |
| Mike | Apples | 20 |
| Mike | Oranges | 30 |
+-------------+---------+----------+
Unpivot columns for each month into a single MONTH column and matching SALES column.
Let's take a look at a really classic example for unpivoting data. Our input has taken the sales from multiple different tables, one for each month, but it's not great to look at. We'd really like to have 2 columns, one for the month and one for the monthly sales numbers.
Input Data
+-----+-----+-----+-----+-----+
| JAN | FEB | MAR | APR | MAY |
+-----+-----+-----+-----+-----+
| 24 | 17 | 33 | 11 | 29 |
+-----+-----+-----+-----+-----+
We're going to make a MONTH column and a SALES column. The MONTH column takes column names from our input while the SALES column takes their values.
Unpivot component properties:
- Columns To Narrow:
- JAN
- FEB
- MAR
- APR
- MAY
- Output Names Column Name: MONTH
- Output Values Column Name: SALES
Output Data
+-------+-------+
| MONTH | SALES |
+-------+-------+
| JAN | 24 |
| FEB | 17 |
| MAR | 33 |
| APR | 11 |
| MAY | 29 |
+-------+-------+
| Snowflake | Databricks | Amazon Redshift |
|---|---|---|
| ✅ | ✅ | ❌ |