Skip to main content

Pivot Transform

Nota

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.

When you pivot data, the values from the selected column or columns become new columns in the dataset, each of which contains a summary calculation that you specify.

  • This calculation can be based on all rows for totals across the dataset or based on group of rows you define in the transform.

  • When your data has been pivoted, the unused columns are dropped, leaving only the operative data for faster evaluation.

  • Transform accepts one or more columns as inputs to the pivot.

Dica

This transform is very useful in Preview mode for quick discovery and analysis.

Basic Usage

pivot col: Dates value:SUM(Sales) group: prodId

Output: Reshapes your dataset to include a ProdId column and new columns for each distinct value in the Dates column. Each distinct ProdId value is represented by a separate row in the reshaped dataset. Within each row, the new columns contain a sum of sales for the product for each date in the dataset.

Syntax and Parameters

pivot col:column_ref value: FUNCTION(arg1,arg2) [group: group_col] [limit:int_num]

Token

Required?

Data Type

Description

pivot

Y

transform

Name of the transform

col

N

string

Source column name or names

value

N

string

Expression containing the aggregation function or functions used to pivot the source column data

group

N

string

Column name or names containing the values by which to group for calculation

limit

N

integer (positive)

Maximum number of unique values in a group. Default is 50.

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

col

Identifies the column or columns to which to apply the transform.

Single-column example:

The specified column contains the values that become new columns in the dataset.

pivot col: autoBrand value:AVERAGE(autoPrice) group:State

Output: Reshapes the dataset to contain the State column followed by columns for each distinct value in the autoBrand column. Each value in those columns is the average value in the autoPrice column, as grouped by State value.

Multi-column example:

For multiple input columns, the transform generates new columns for each combination of the inputs. See the example below.

Usage Notes:

Required?

Data Type

No

String (column name)

value

For this transform, the value parameter contains the aggregation function and its parameters, which define the set of rows to which the function is applied.

Nota

For the value parameter, you can only use aggregation functions. Nested functions are not supported. For more information, see Aggregate Functions.

Usage Notes:

Required?

Data Type

No

String containing a list of aggregation functions each containing one column reference

group

For this transform, this parameter specifies the column whose values are used to group the dataset prior to applying the specified function. You can specify multiple columns as comma-separated values.

Nota

Transforms that use the group parameter can result in non-deterministic re-ordering in the data grid. However, you should apply the group parameter, particularly on larger datasets, or your job may run out of memory and fail. To enforce row ordering, you can use the sort transform. For more information, see Sort Transform.

Usage Notes:

Required?

Data Type

No

String (column name)

limit

The limit parameter defines the maximum number of unique values permitted in a group. If it is not specified, the default value for this parameter is 50.

Nota

Be careful setting this parameter too high. In some cases, the application can run out of memory generating the results, and your results can fail.

Usage Notes:

Required?

Data Type

No. Default value is 50.

Integer (positive)

Examples

Dica

For additional examples, see Common Tasks.

Example - Basic Pivot

Source:

The following dataset contains information about sales across one weekend and two states for three different products.

Date

State

ProdId

Sales

3/9/16

CA

Big Trike 9000

500

3/9/16

NV

Fast GoKart 5000

200

3/9/16

CA

SuperQuick Scooter

700

3/9/16

CA

Fast GoKart 5000

900

3/9/16

NV

Big Trike 9000

300

3/9/16

NV

SuperQuick Scooter

250

3/10/16

NV

Fast GoKart 5000

50

3/10/16

NV

Big Trike 9000

400

3/10/16

NV

SuperQuick Scooter

150

3/10/16

CA

Big Trike 9000

600

3/10/16

CA

SuperQuick Scooter

800

3/10/16

CA

Fast GoKart 5000

1100

Transformation 1: Sum of sales by State for each Date

Apply this transformation in Preview only, just so you can see the results:

Transformation Name

Pivot columns

Parameter: Column labels

Date

Parameter: Row labels

State

Parameter: Values

sum(Sales)

Cancel the transformation update.

State

sum_Sales_03/09/2016

sum_Sales_03/10/2016

CA

2100

2500

NV

750

600

Transform 2: Sum of Sales by Date for each State

Transformation Name

Pivot columns

Parameter: Column labels

State

Parameter: Row labels

Date

Parameter: Values

sum(Sales)

Date

sum_Sales_CA

sum_Sales_NV

03/09/2016

2100

750

03/10/2016

2500

600

Cancel the transformation update again.

Transform 3: Sum of Sales by product ID for each State

Transformation Name

Pivot columns

Parameter: Column labels

State

Parameter: Row labels

ProdId

Parameter: Values

sum(Sales)

ProdId

sum_Sales_CA

sum_Sales_NV

Big Trike 9000

1100

700

Fast GoKart 5000

2000

500

SuperQuick Scooter

1500

300

Example - Multi-column Pivot

The Pivot Columns transformation supports the ability to use the values in multiple columns to specify the columns that are generated. When two or more columns are used in the pivot, columns containing values of all possible combinations from the source columns are generated.

In the following source data, sales data on individual products is organized by brand, product name, and month:

Brand

Product

Month

Sales

AAA

Towels

January

95

AAA

Napkins

January

113

B

Towels

January

99

B

Tissues

January

88

AAA

Towels

February

108

AAA

Napkins

February

91

B

Towels

February

85

B

Tissues

February

105

AAA

Towels

March

81

AAA

Napkins

March

92

B

Towels

March

112

B

Tissues

March

104

Transformation:

If you wanted to create summary sales information for each product by month, you might choose to create a pivot on the Product column. However, if you look at the column values, you might notice that the Paper towels product is available for both the AAA and B brands. In this case, you must perform a multi-column pivot on these two columns, like the following:

Transformation Name

Pivot columns

Parameter: Column labels

Brand,Product

Parameter: Row labels

Month

Parameter: Values

average(Sales)

Results:

Month

avg_Sales_B_Towels

avg_Sales_B_Tissues

avg_Sales_AAA_Napkins

avg_Sales_AAA_Towels

January

99

88

113

95

February

85

105

91

108

March

112

104

92

81

Example - Aggregate Values

You can use the pivot transform to perform aggregation calculations on values in a column.

Source:

In the following table, you can review test results from three different tests for 10 students:

StudentId

TestId

Score

s001

t001

98

s001

t002

98

s001

t003

87

s002

t001

92

s002

t002

96

s002

t003

79

s003

t001

99

s003

t002

76

s003

t003

94

s004

t001

93

s004

t002

99

s004

t003

80

s005

t001

79

s005

t002

80

s005

t003

84

s006

t001

93

s006

t002

74

s006

t003

89

s007

t001

86

s007

t002

81

s007

t003

97

s008

t001

82

s008

t002

73

s008

t003

79

s009

t001

96

s009

t002

97

s009

t003

79

s010

t001

80

s010

t002

79

s010

t003

75

Transformation:

For each student, you're interested in a student's average score and maximum score. You create the following pivot transform, which groups computations of average score and maximum score columns by studentId value.

Transformation Name

Pivot columns

Parameter: Row labels

StudentId

Parameter: Values

average(Score),max(Score)

Parameter: Max number of columns to create

50

For the generated average column, you want results rounded to two decimal places:

Transformation Name

Edit column with formula

Parameter: Columns

average_Score

Parameter: Formula

round(average_Score, 2)

Results:

StudentId

average_Score

max_Score

s001

94.33

98

s002

89

96

s003

89.67

99

s004

90.67

99

s005

81

84

s006

85.33

93

s007

88

97

s008

78

82

s009

90.67

97

s010

78

80