Pivot
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
A human-readable name for the component.
Aggregate Function = drop-down
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
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
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
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
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 product
We 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
| Product | Month | Sales |
|---|---|---|
| T-Shirt | Jan | 100 |
| T-Shirt | Jan | 50 |
| T-Shirt | Feb | 150 |
| T-Shirt | Mar | 200 |
| Hoodie | Jan | 200 |
| Hoodie | Jan | 100 |
| Hoodie | Mar | 250 |
| Hoodie | Mar | 180 |
| Cap | Jan | 60 |
| Cap | Feb | 90 |
| Cap | Mar | 120 |
Component configuration
- Aggregate Function: Sum
- Pivot Column: Month
- Value Column: Sales
- Pivot Values: Jan, Feb, Mar
Output data
| Product | Jan | Feb | Mar |
|---|---|---|---|
| T-Shirt | 150 | 150 | 200 |
| Hoodie | 300 | 430 | |
| Cap | 60 | 90 | 120 |
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.
Goal: Calculate average student exam results by subject
We have the following table, containing different students' exam results for a range of subjects. Let's use the Pivot component to calculate each student's average score in each subject. The students are identified by their student ID number.
Input data
| Student_ID | Subject | Score |
|---|---|---|
| S001 | Science | 85 |
| S001 | Science | 90 |
| S001 | History | 78 |
| S002 | Geography | 88 |
| S002 | History | 92 |
| S002 | History | 86 |
Component configuration
- Aggregate Function: Average
- Pivot Column: Subject
- Value Column: Score
- Pivot Values: Science, History, Geography
Output data
| Student_ID | Science | History | Geography |
|---|---|---|---|
| S001 | 87.5 | 78 | |
| S002 | 89 | 88 |
Result: The pivot values Science, History, and Geography are used as the new column names. The values in the value column Score are aggregated using the Average function and grouped by the values in the pivot column Subject. There are two null values here, as student S001 has no score for Geography and S002 has no score for Science.