Skip to main content

Unpivot Transform

Note

Transforms are a part of the underlying language, which is not directly accessible to users. This content is maintained for reference purposes only. For more information on the user-accessible equivalent to transforms, see Transformation Reference.

Reshapes the data by merging one or more columns into key and value columns. Keys are the names of input columns, and value columns are the cell values from the source.

Rows of data are duplicated, once for each input column.

The unpivot column can be applied to multiple columns in the same transform. All columns are un-pivoted into the same key and value columns. When this transform is applied to two columns, the number of rows in the dataset is doubled.

This transform is the opposite of thepivottransform, which converts a set of column values into distinct columns. SeePivot Transform.

Basic Usage

Single- or multi-column example:

You can specify single columns or comma-separated sets of columns.

unpivot col: FirstName, MiddleInitial

Output: Converts the values in the columns FirstName and MiddleInitial into separate key and value columns.

Column range example:

You can also specify ranges of columns using the tilde (~) operator:

unpivot col:Column1~Column20

Output: Converts all of the values in columns between Column1 and Column20 into key and value columns.

Syntax and Parameters

unpivot col: column_ref [groupEvery: int_num]

Token

Required?

Data Type

Description

unpivot

Y

transform

Name of the transform

col

Y

string

Name of source column or columns

groupEvery

N

string

If specified, this parameter defines the number of individual key-value pairs to store in each generated column. Default is 1.

For more information on syntax standards, see Language Documentation Syntax Notes.

col

Identifies the column or columns to which to apply the transform. You can specify one or more columns.

To specify multiple columns:

  • Discrete column names are comma-separated.

  • Values for column names are case-sensitive.

  • Column ranges are supported:

    myColumn1~myColumn5

Note

For the col value, you can use the asterisk ( * ) wildcard 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.

Usage Notes:

Required?

Data Type

Yes

String (column name)

groupEvery

Specifies the number of output key-value pair columns to produce after unpivoting.

This optional parameter is used to create multiple sets of key-value pair columns in the output. The columns listed in the col parameter are placed into each pair of output key-value columns sequentially. After all key-value pair columns are filled in a record, the next column is placed into the first key-value pair column of the next record.

By default, this value is 1, meaning that each column specified in the transform is rendered into a new record in a single pair of key-value columns.

Usage Notes:

Required?

Data Type

No

Integer (positive)

Examples

Tip

For additional examples, see Common Tasks.

Example - Basic Unpivot

Source:

productName

productColor

productSize

Whizbang

red

M

Whizbang

red, blue

L

Whizbang

green

M

Bangwhiz

red

S

Bangwhiz

blue

M

Bangwhiz

red

S

Tranformation:

After you have created a header, if necessary, add the following transformation:

Transformation Name

Unpivot columns

Parameter: Columns

productColor

Results:

productName

productSize

key

value

Whizbang

M

productColor

red

Whizbang

L

productColor

red, blue

Whizbang

M

productColor

green

Bangwhiz

S

productColor

red

Bangwhiz

M

productColor

blue

Bangwhiz

S

productColor

red

Extended:

Note how each instance of a value results in a separate row; duplicate values are included. For a single-column unpivot, this transform results in the same number of rows as the source.

  • Since the value is treated as a string, the value red, blue is treated as one value.

Now, edit the transformation you just added. Replace it with the following, which includes the productSize key as part of the transformation:

Transformation Name

Unpivot columns

Parameter: Columns

productColor,productSize

Results:

productName

key

value

Whizbang

productColor

red

Whizbang

productSize

M

Whizbang

productColor

red, blue

Whizbang

productSize

L

Whizbang

productColor

green

Whizbang

productSize

M

Bangwhiz

productColor

red

Bangwhiz

productSize

S

Bangwhiz

productColor

blue

Bangwhiz

productSize

M

Bangwhiz

productColor

red

Bangwhiz

productSize

S

Row keys alternate based on the order in which the source columns are specified in the transform. Since the transform specifies two columns, the number of key-value pairs is doubled, which results in a dataset that has twice as many rows as the source.

Example - Basic Pivot with groupEvery

Tranformation:

From the previous example, modify the unpivot transform to be the following:

Transformation Name

Unpivot columns

Parameter: Columns

productColor,productSize

Parameter: Group size

2

Results:

productName

key1

value1

key2

value2

Whizbang

productColor

red

productSize

M

Whizbang

productColor

red, blue

productSize

L

Whizbang

productColor

green

productSize

M

Bangwhiz

productColor

red

productSize

S

Bangwhiz

productColor

blue

productSize

M

Bangwhiz

productColor

red

productSize

S