Pivot Transform

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

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] 

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:

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. 

Usage Notes:

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.

Usage Notes:

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.

Usage Notes:

Examples

Example - Basic Pivot

Source:

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

Transformation 1: Sum of sales by State for each Date

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

Cancel the transformation update.

Transform 2: Sum of Sales by Date for each State

Cancel the transformation update again.

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

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:

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:

Results:

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:

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.

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

Results: