User Tools

Site Tools


transformations:unpivot

UNPIVOT

Category: Transform / Advanced


Description

Converts a matrix table into a straight table by rotating columns into "Labels" and "Data" columns. This action is opposite to the Pivot action.


Use cases

This action is used to convert a cross-table (or matrix) into a straight table. It can be used for instance to convert a cross-table from an Excel spreadsheet into a table suitable for exporting into a database.


Action settings

SettingDescription
Column name for labelsEnter a name for the column that will contain the column header labels from the matrix table.
Column name for dataEnter a name for the column that will contain the data values from the matrix table.
Unpivot all columns except theseSelect fields that will not be unpivoted, but instead will be repeated down rows, as is, once for each column heading in the matrix table.


Remarks

Typically, any columns used for row labels in the source matrix table will be selected under "Unpivot all columns except these" to retain those values as row values in the unpivoted table. Otherwise, those values will be listed in the result table with a "label" equal to the matrix table's column heading for the row labels, and data values equal to the row labels.


Examples

Objective: Convert the matrix table, below, into a standard data table.

Source table: Winter Olympics medals

Country winter 2010 winter 2014
Canada 26 25
United States 37 28
Norway 23 26
Germany 30 19


Action parameters:

Column name for labels is "Labels"
Column name for data is "Data"
Unpivot all columns except these = "Country" selected


Result:

Country Labels Data
Canada winter 2010 26
United States winter 2010 37
Norway winter 2010 23
Germany winter 2010 30
Canada winter 2014 25
United States winter 2014 28
Norway winter 2014 26
Germany winter 2014 19

Notice how the original set of Country values repeat for each value in Labels.


If no columns were selected under "Unpivot all columns except these", the resultant table would appear as below:

Result:

Labels Data
Country Canada
Country United States
Country Norway
Country Germany
winter 2010 26
winter 2010 37
winter 2010 23
winter 2010 30
winter 2014 25
winter 2014 28
winter 2014 26
winter 2014 19


See also

transformations/unpivot.txt · Last modified: 2021/04/28 14:33 by dmitry