How to use Data Services Reverse Pivot Transformation
In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each selected pivot column, Data Services produces a column in the output data set. It basically creates one row of data from several existing rows, i.e. to Pivot Rows to Columns.
This article is part of our comprehensive data services tutorial Learning SAP Data Services - Online Tutorial, you may want to check that tutorial first if you have not already done so.
Implementation
Let us consider we have source data of sales for different stores based on quarters. We may want to transform the sales data based on the Stores for the four quarters. Find below the implementation Data Flow.
To solve the problem we are using the SAP Data Services <b>Reverse Pivot</b> transform. We already have sorted the source data based on the Non-pivot column SHOP. Next we select the SALES as the required Pivoted column and QUARTER column as the Pivot Axis. The quarter values are basically 1,2,3 and 4. So the Axis values have been set accordingly.
Finally we map the sales for the quarters for each shop accordingly in the Query transform. 1_SALES mapped to Quarter1, 2_SALES to Quarter2 so on.
Transform Options
- Non-pivot columns : The columns in the input schema that will appear in the output schema without any modifications.
- Input data is grouped : Enable this option if the input rows are already sorted based on columns specified in the "Non-pivot columns" list. This will improve the performance of the transformation.
- Pivoted columns : The columns containing data that we want to rotate or convert into the same row. A set of columns will be created for each unique values in the Pivot axis column.
- Default value : The value stored when the rotated column has no corresponding data. The default is "null" if we do not enter a value. Do not enter a blank.
- Pivot axis column : The column that determines what new columns are needed in the output schema. At run time, a new column is created for each Pivoted column and each unique value in this column.
- Axis value : The value of the pivot axis column that represents a particular set of output columns. A set of Pivoted columns is generated for each axis value. There should be one Axis value for each unique value in the Pivot axis column.
- Column Prefix : Text added to the front of the Pivoted column names when creating new column names for the rotated data. An underscore separates the prefix name from the pivoted column name.
- Duplicate value : Action taken when a collision occurs. A collision occurs when there is more than one row with the same key and value in the Pivot axis column. In this case, we can select either the first row or the last row, or we can abort the transformation process.
