Page tree


Support | BlogContact Us | 844.332.2821

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

D toc


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.

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

D s

D code
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.  

D s
D code

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

ParameterRequired?Transform BuilderData TypeDescription
colYColumnstringSource column name or names
valueYFunctionsstringExpression containing the aggregation function or functions used to pivot the source column data
groupNGroup bystringColumn name or names containing the values by which to group for calculation
limitNMax columns to createinteger (positive)Maximum number of unique values in a group. Default is 50.



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.

D code

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.

D s

Required?Data Type
YesString (column name)


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 .

D s

Required?Data Type
YesString containing a list of aggregation functions each containing one column reference


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.

D s

Required?Data Type
NoString (column name)


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.

D s

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

D s

Example - Basic Pivot


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

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

Transform 1: Sum of sales by State for each Date

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

D code

pivot col:Date value:SUM(Sales) group:State

Cancel the transform update.


Transform 2:
Sum of Sales by Date for each State

D code

pivot col:State value:SUM(Sales) group:Date


Cancel the transform update again.

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

D code

pivot col:State value:SUM(Sales) group:ProdId

Big Trike 90001100700
Fast GoKart 50002000500
SuperQuick Scooter1500300

Example - Multi-column Pivot

The pivot transform 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:

Super-coolPaper towelsJanuary95
Just-coolPaper towelsJanuary99
Super-coolPaper towelsFebruary108
Just-coolPaper towelsFebruary85
Super-coolPaper towelsMarch81
Just-coolPaper towelsMarch112


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 Super-cool and Just-cool brands. In this case, you must perform a multi-column pivot on these two columns, like the following:

D code

pivot col:Brand,Product value:AVERAGE(Sales) group:Month


Monthaverage_Sales_Just_cool_Paper_Towels average_Sales_Just_cool_Paper_Tissues average_Sales_Super_cool_Napkins average_Sales_Super_cool_Paper_Towels

D s also