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 | 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 | |
---|---|
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 | |
---|---|
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 | |
---|---|
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 | |
---|---|
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 | |
---|---|
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 | |
---|---|
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 |