Use of Pivot Transformation in Data Services
The Pivot transformation allows us to change how the relationship between rows is displayed. For each value in each pivot column, Data Services produces a row in the output data set. It basically converts Columns to Rows.
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 quarters for each of the stores.
To solve the problem we are using the SAP Data Services Pivot transform. We first set the Pivot sequence column value as PIVOT_SEQ. So the sequence value will be 1 for QUARTER1 column, 2 for QUARTER2 column .. when being transformed to multiple rows. Select the STORE as the Non-Pivoted column. In this example we are dealing with only one Pivot set for QUARTER. Hence the Pivot set is set to 1. Next include all the input QUARTER columns as the Pivot Columns. Set the output Header column name as QUARTER and the corresponding Data fields as SALES.
Finally we map the quarter to the Pivot Sequence in the Query transform. So for the first quarter sales value the corresponding QUARTER will be 1 and so on.