Skip to main content
The Pivot transformation component lets you turn your rows of data into columns. This generally means reducing the number of rows and increasing the number of columns. The Pivot column you choose contains the data that you want to aggregate. The Value column you choose contains labels for that data, which define the groups by which the data in the Pivot column can be aggregated. The Pivot Values you specify (values in the Value column) are used as the new column names in the output. This component is equivalent to an SQL PIVOT function.

​
Use case

This component can be used to analyze data by aggregating values from a single dataset in different categories. For example, you can use it to:
  • Calculate total sales per product using a dataset containing data from multiple individual sales.
  • Monitor average product downtime per day using a dataset containing downtime data for multiple products over a longer period of time.

​
Properties

​
Name
string
required
A human-readable name for the component.
​
Aggregate Function
drop-down
required
Select which aggregate function to call to combine the grouped values from the Pivot Column. For more information, read the following:
​
Pivot Column
drop-down
required
Specify the column from the source table or sub-query that will be aggregated. This requires an input component before configuring and uses the column names from the input component.The data type expected in this column depends on the aggregate function you are depending to use. For example, you cannot average non-numeric values.
​
Value Column
drop-down
required
Specify the column from the source table or sub-query that contains the values that will group data for aggregations and from which new column names will be generated. This requires an input component before configuring and uses the column names from the input component.The chosen Value Column will be missing from the output, replaced by a new column for each of the Pivot Values specified.
​
Pivot Values
multiple strings
required
A list of values that exist in the Value Column in the input data that are used to group data for aggregation. These values become new columns in the output, with data from the Pivot Column being aggregated into these new columns.
​
Quote Pivot Value Columns
drop-down
required
Choose whether to enclose the new column names (the selected pivot values) in quote marks. This can avoid problems with the component if your pivot values contain spaces or special characters, if your pivot values are numeric data, or if your data is in mixed case.If you use Snowflake, we recommend selecting Yes because Snowflake has specific rules for column identifiers. Enclosing your pivot values in quotes helps to meet these rules.

​
Examples

Goal: Calculate total monthly sales for each productWe have the following table, containing data from individual sales of three different products (caps, T-shirts, and hoodies) in January, February, and March. Let’s use the Pivot component to group the sales data by month and calculate the total sales for each month.Input data
ProductMonthSales
T-ShirtJan100
T-ShirtJan50
T-ShirtFeb150
T-ShirtMar200
HoodieJan200
HoodieJan100
HoodieMar250
HoodieMar180
CapJan60
CapFeb90
CapMar120
Component configuration
  • Aggregate Function: Sum
  • Pivot Column: Month
  • Value Column: Sales
  • Pivot Values: Jan, Feb, Mar
Output data
ProductJanFebMar
T-Shirt150150200
Hoodie300430
Cap6090120
Result: The pivot values Jan, Feb, and Mar are used as the new column names. The values in the value column Sales are aggregated using the Sum function and grouped by the values in the pivot column Month. There was no sales data for hoodies in February, so this value is null.