Contents:
- Unpivot can be applied to one or more columns.
- Often, this transformation is applied to datasets containing pivoted or aggregated data.
NOTE: Depending on the number of source columns, an unpivot operation can significantly increase the number of rows in your dataset.
Single-column Unpivot
When you unpivot a single column of data, the column is separated into two new columns in your dataset:
| New column name | Values |
|---|---|
key | All values are the name of the source column. |
value | Each row contains one of the row values from the source column. |
NOTE: These columns replace the source column in the dataset. To retain the source column, create a copy of it first and then unpivot the copied column.
Source:
The following example contains a very simple set of data:
| Name | favoriteColor | favoriteDessert |
|---|---|---|
| Anna | red | ice cream |
| Bella | pink | cookies |
| Callie | blue | pie |
Transformation:
You can unpivot these columns one-by-one into row data:
| Transformation Name | Unpivot columns |
|---|---|
| Parameter: Columns | favoriteColor |
| Parameter: Group size | 1 |
Results:
The new unpivoted columns are placed at the end of the dataset, and the source column is removed.
| Name | favoriteDessert | key | value |
|---|---|---|---|
| Anna | ice cream | favoriteColor | red |
| Bella | cookies | favoriteColor | pink |
| Callie | pie | favoriteColor | blue |
Multi-column Unpivot
This example turns the data from multiple columns into a single set of key-value pairs, where the key is the column name associated with the source of the data in the value column.
Source:
The following dataset shows student test scores per test. Each row represents the scores of individual students.
| StudentId | test1Score | test2Score | test3Score |
|---|---|---|---|
| 001 | 75 | 79 | 77 |
| 002 | 84 | 81 | 86 |
| 003 | 79 | 82 | 87 |
| 004 | 92 | 94 | 92 |
Transformation:
You can use the following transformation to turn the dataset into one row per student-test combination:
| Transformation Name | Unpivot columns |
|---|---|
| Parameter: Columns | test1Score, test2Score, test3Score |
| Parameter: Group size | 1 |
Results:
The results are as follows:
| StudentId | key | value |
|---|---|---|
| 001 | test1Score | 75 |
| 002 | test2Score | 79 |
| 003 | test3Score | 77 |
| 001 | test1Score | 84 |
| 002 | test2Score | 81 |
| 003 | test3Score | 86 |
| 001 | test1Score | 79 |
| 002 | test2Score | 82 |
| 003 | test3Score | 87 |
| 001 | test1Score | 92 |
| 002 | test2Score | 94 |
| 003 | test3Score | 92 |
You can then rename the key and value columns as needed.
Ranges
You can specify a range of columns in your dataset. In the previous example, you can specify the three test score columns using the following value in the Columns textbox:
test1Score~test3Score
All three columns are unpivoted.
Wildcards
NOTE: You can use the asterisk ( * ) wildcard in the Columns textbox to apply the unpivot to the entire dataset, which generates a key and a value column, containing all column-row entries from the source columns. However, unpivoting a large number of columns can significantly increase the number of rows in your dataset.
This page has no comments.