In ETL pipelines, source data often requires reshaping to meet the requirements of target systems or reporting layers. You can transform data structures—such as converting columns to rows or vice versa—without writing complex custom code.
SAP Data Services provides two complementary Data Integrator transforms for this purpose: Pivot and Reverse Pivot.
The Pivot transform rotates specific columns into rows. It normalizes wide, horizontal datasets into a vertical format by creating a new row for each value in a pivot column. For example, you can convert multiple discount-type columns into individual records (one row per discount per payment term) to simplify downstream processing or joins.
The Reverse Pivot transform performs the opposite operation: it aggregates data from multiple rows into a single row. It generates new columns based on the unique values of a pivot axis. This is useful for creating matrix-like layouts, such as consolidating quarterly sales records into a single store record with separate columns for each quarter.
Pivot Transform Options
The following table describes the options available in the Pivot transform:
Pivot Transform Options
| Option | Description |
|---|---|
Pivot Sequence Column | Specifies a name for the column that stores an incrementing sequence number for each row generated from a pivot column. |
Non-Pivot Columns | Identifies source columns to be passed to the target without modification. |
Pivot Set | Defines a group of pivot columns, a pivot data field, and a pivot header name for each set. |
Data Column Field | Specifies the column that will contain the values from the pivoted columns. |
Header Column | Specifies the column that will store the original names of the pivoted columns. |
Pivot Columns | Identifies the specific columns to be rotated into rows. These are described by the Header Column and Data Column Field. |
Summary
In this lesson, you learned how to reshape data using SAP Data Services transforms:
- Reshape data to meet target system requirements using the Pivot or Reverse Pivot transforms without custom code.
- Use the Pivot transform to rotate columns into rows, normalizing wide datasets into a vertical format.
- Use the Reverse Pivot transform to combine data from multiple rows into a single row by generating new columns.
- Configure Pivot Sets, Data Column Fields, and Header Columns to map source data to the target schema.