Reverse Pivot Transformation – Multiple in Data Services
Here I am not disclosing the business requirement but just want to illustrate with sample job…please have a look….
What is Reverse Pivot ?
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.
The below is the source file where it has cost center information comprises currencies with respect to debit, credit and ledgers (00 and 10).
- Ledger value is a column provided for identifying different data for same cost element/general ledger
- Creation of format files for excel is not covered
Create the format for excel work book, drag and drop source file and pivot transform in the work area .Map it to pivot transform as shown below.
Double click the Reverse pivot transform,
- select the non-pivot columns from source and drag it to Non-Pivot Columns section
Place a another Reverse pivot transform and fill the properties as shown in the image below.
Select the target table where the data to be loaded and map it.
Validate and execute the job and check the source and Target data details as follows. (Four rows information is converted to single row)
SOURCE (four rows)
TARGET (Single row)
Hope this information will be helpful for you…