(888) 884 6405
Press Enter to see full results

ETL & Reverse ETL
Knowledge Base

Using components: Pivot Transformation

The Pivot transformation component converts rows into columns by aggregating values based on specified pivot values. The pivot component is useful for restructuring data where you want to transform categorical data from rows into columns, making it easier to analyze and compare values across different categories.

Configuration

1. Pivot Propertiesthumbnail image

Aggregate functions

  • Sum - calculates the total sum of values for each pivot value
  • Average - calculates the average of values for each pivot value
  • Count - counts the number of records for each pivot value
  • Min - finds the minimum value for each pivot value
  • Max - finds the maximum value for each pivot value

Pivot column

Select the column whose values will become column headers in the output. This column should contain the categorical values that you want to transform from rows to columns.

Value column

Select the column whose values will be aggregated. This column should contain the numeric values that you want to sum, average, count, or find min/max for each pivot value.

Pivot values

Specify the exact values that should become column headers. Only records with these specific values in the pivot column will be included in the output. 

Example 1

Input data:

Student_ID Subject Score
1 Science 85
1 History 92
2 Science 78
2 History 88
2 Science 100

Pivot configuration:

  • Pivot column: Subject
  • Value column: Score
  • Pivot values: ["Science", "History"]
  • Aggregate function: Sum

Output data:

Student_ID Science History
1 85 92
2 178 88

Example 2

Input data:

Salesperson Category Revenue Quarter
John Electronics 15000 Q1
John Clothing 8000 Q1
John Electronics 18000 Q2
John Clothing 12000 Q2
Sarah Electronics 20000 Q1
Sarah Clothing 9000 Q1
Sarah Electronics 22000 Q2
Sarah Clothing 11000 Q2

Pivot configuration:

  • Pivot column: Category
  • Value column: Revenue
  • Pivot values: ["Electronics", "Clothing"]
  • Aggregate function: Sum
Salesperson Quarter Electronics Clothing
John Q1 15000 8000
John Q2 18000 12000
Sarah Q1 20000 9000
Sarah Q2 22000 11000

Important notes

  • Make sure the value column contains numeric data for proper aggregation
  • Empty pivot values will cause the component to pass through data unchanged




See Also