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.

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

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

Output: Reshapes your dataset to include 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] 

TokenRequired?Data TypeDescription
pivotYtransformName of the transform
colNstringSource column name or names
valueNstringExpression containing the aggregation function or functions used to pivot the source column data
groupNstringColumn name or names containing the values by which to group for calculation
limitNinteger (positive)Maximum number of unique values in a group. Default is 50.

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.

Required?Data Type
NoString (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. 

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

Required?Data Type
NoString 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.

Required?Data Type
NoString (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.

NOTE: 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.

Required?Data Type
No. Default value is 50.Integer (positive)


Example - Basic Pivot

Source:

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

DateStateProdIdSales
3/9/16CABig Trike 9000500
3/9/16NVFast GoKart 5000200
3/9/16CASuperQuick Scooter700
3/9/16CAFast GoKart 5000900
3/9/16NVBig Trike 9000300
3/9/16NVSuperQuick Scooter250
3/10/16NVFast GoKart 500050
3/10/16NVBig Trike 9000400
3/10/16NVSuperQuick Scooter150
3/10/16CABig Trike 9000600
3/10/16CASuperQuick Scooter800
3/10/16CAFast GoKart 50001100

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.

Statesum_Sales_03/09/2016sum_Sales_03/10/2016
CA21002500
NV750600


Transform 2:
Sum of Sales by Date for each State

Datesum_Sales_CAsum_Sales_NV
03/09/20162100750
03/10/20162500600

Cancel the transformation update again.

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

ProdIdsum_Sales_CAsum_Sales_NV
Big Trike 90001100700
Fast GoKart 50002000500
SuperQuick Scooter1500300

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:

BrandProductMonthSales
AAATowelsJanuary95
AAANapkinsJanuary113
BTowelsJanuary99
BTissuesJanuary88
AAATowelsFebruary108
AAANapkinsFebruary91
BTowelsFebruary85
BTissuesFebruary105
AAATowelsMarch81
AAANapkinsMarch92
BTowelsMarch112
BTissuesMarch104

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:

Monthavg_Sales_B_Towelsavg_Sales_B_Tissuesavg_Sales_AAA_Napkinsavg_Sales_AAA_Towels
January998811395
February8510591108
March1121049281

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:

StudentIdTestIdScore
s001t00198
s001t00298
s001t00387
s002t00192
s002t00296
s002t00379
s003t00199
s003t00276
s003t00394
s004t00193
s004t00299
s004t00380
s005t00179
s005t00280
s005t00384
s006t00193
s006t00274
s006t00389
s007t00186
s007t00281
s007t00397
s008t00182
s008t00273
s008t00379
s009t00196
s009t00297
s009t00379
s010t00180
s010t00279
s010t00375

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:

StudentIdaverage_Scoremax_Score
s00194.3398
s0028996
s00389.6799
s00490.6799
s0058184
s00685.3393
s0078897
s0087882
s00990.6797
s0107880