Pivot Transform

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.

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.

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:

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:

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:

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:

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:

Date

State

sum(Sales)

Cancel the transformation update.

Transform 2: Sum of Sales by Date for each State

State

Date

sum(Sales)

Cancel the transformation update again.

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

State

ProdId

sum(Sales)

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:

Brand,Product

Month

average(Sales)

Results:

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.

StudentId

average(Score),max(Score)

50

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

average_Score

round(average_Score, 2)

Results: