clsPivotTransform Step

Use this feature to reorganize tabular data into a pivoted format by grouping rows, creating dynamic columns, and aggregating values. This transformation simplifies reporting, analytics, and business intelligence by converting row-based data into a summa

Last published at: July 20th, 2026

Description:

The Pivot Transform step converts row-oriented datasets into a pivot table structure by grouping records, creating columns from distinct values, and aggregating numeric or calculated data.

Unlike standard sorting or aggregation transforms, a pivot transformation restructures the dataset itself. Designers configure one or more row grouping fields, specify the column that should generate new pivot columns, and define the value field and the aggregation function to apply.

Optionally, the transformation can calculate grand totals for rows and columns, making it ideal for reporting and dashboard scenarios.

The Pivot Transform step is typically used after data has been imported, cleansed, and validated, but before exporting reports or loading summarized data into reporting databases.

The step supports:

  • Row grouping
  • Dynamic pivot columns
  • Aggregated values
  • Multiple aggregation methods
  • Row grand totals
  • Column grand totals
  • Enterprise ETL reporting workflows
  • Integration with FlowWright ETL pipelines

This step can be used for:

  • Sales reports
  • Financial summaries
  • Inventory analysis
  • Departmental reporting
  • Business intelligence
  • Data warehousing
  • Executive dashboards

 

Inputs

  • Select Pivot Rows – Select one or more columns that define the row groups in the pivot table. Records sharing identical values are grouped together.
  • Select Pivot Columns – Select the column whose distinct values become the generated pivot columns.
  • Select Pivot Value – Configure the column whose values are aggregated, along with the aggregation method (for example, Sum, Count, Average, Minimum, or Maximum), depending on the configured aggregation options.
  • Show Row Grand Total – Specify whether a grand total should be calculated for each row.
  • Show Column Grand Total – Specify whether a grand total should be calculated for each generated pivot column.
 

 

Returns

  • The Pivot Transform step does not define explicit return paths.

Note: After the pivot table has been generated successfully, ETL processing automatically continues to the next connected component. Configuration or execution errors are reported through the ETL execution log.

 

 

Usage:

The Pivot Transform step is typically inserted after filtering, validation, or data cleansing operations, and before exporting summarized data or generating analytical reports.

During execution, the transformation groups records by the selected pivot rows, creates new columns from the configured pivot column values, applies the specified aggregation, and optionally calculates row and column grand totals.

A typical ETL pipeline might look like this:

 

Typical pivot scenarios include:

  • Monthly sales by region
  • Product sales by quarter
  • Employee hours by department
  • Revenue by business unit
  • Inventory quantities by warehouse
  • Expenses by cost center
  • Customer purchases by product category

The resulting pivot table provides a summarized dataset that is easier to analyze and consume in reports or dashboards.

 

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

 

 

Example:

Let’s build and execute the “clsPivotTransformDef” example.          

  • Create a new ETL definition named “clsPivotTransformDef” and open it in designer mode.
  • Drag the “clsInputCSV, clsPivotTransform, and clsOutputCSV” steps to the canvas. 
  • Connect the dots between the “clsInputCSV” and other steps, as shown above.
  • Define a variable or a global to hold the virtual path. 
  • Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

 

  • Click the "clsInputCSV" step to configure its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

 

  • Click the "clsPivotTransform" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the Pivot Columns. Then click the Save button. 

 

  • Click the button to configure the Pivot Columns. A pop-up window appears for configuration. Click the Add Row (+) button to insert an empty row. Select the column name from the drop-down list. Click the Save button. You may add multiple pivot columns for transformation by using the Add Row button.   

 

  • Click the button to configure the Pivot Values. A pop-up window appears for configuration. Click the Add Row (+) button to insert an empty row. Select the column name from the drop-down list. Select the pivot operation from the drop-down list below. Click the Save button. You may add multiple pivot values for transformation by using the Add Row button. 

 

  • Click the "clsPivotTransform" step to configure its "Optional" properties. A pop-up window appears for configuration. Click the button to select Pivot Rows. Click the Add Row (+) button to insert an empty row. Select the column name from the drop-down list. Click the Save button. You may add multiple pivot rows for transformation by using the Add Row button. Select “Yes” to include the total computation for the row and column. 

 

  • The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

 

 

  • Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

 

  • Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

 

  • Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. The “clsPivotTransform” step should calculate and summarize numeric data using the Pivot function. The variable or global holds the output file’s virtual path as shown below.  The summarized dataset can then be exported, loaded into a reporting database, or consumed by business intelligence applications.

 

  • The ETL transform pivot output file is included for reference.

 

  • A similar pivot function output from Excel is provided here for reference.  

 

Tips:

  • Clean and validate data before performing pivot operations.
  • Use row grouping fields that uniquely identify logical groups within the dataset.
  • Select aggregation methods that align with your reporting requirements.
  • Enable grand totals only when they provide meaningful analytical value.
  • Limit the number of pivot columns when working with datasets containing many distinct values to improve readability and performance.
  • Verify the generated pivot table using representative sample data before deploying to production.
  • Combine this transform with Validate Data, Sorting Transform, Aggregate Transform, Remove Duplicates, Rename Column, or JSON Output File components to build comprehensive ETL reporting solutions.

 

Notes:

  • Pivot operations reorganize the dataset into a summarized matrix format.
  • The selected pivot column determines the dynamically generated output columns.
  • Aggregation is performed using the configured value field and aggregation method.
  • Row grouping is optional but is typically used to logically organize summarized data.
  • Grand totals are optional and can be enabled independently for rows and columns.
  • Large datasets or pivot operations involving many distinct column values may require additional processing time and memory.
  • Invalid schema mappings or aggregation configurations may result in ETL execution errors.
  • This transform is commonly used in financial reporting, operational dashboards, business intelligence, inventory analysis, sales reporting, and enterprise analytics.

 

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

  • Pivot row selection
  • Pivot column selection
  • Pivot value and aggregation configuration
  • Row and column grand total settings
  • ETL schema validation
  • Downstream destination mappings
  • Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.