Note on Reverse Pivot: This transform converts rows in to columns. It will group the data-set of different rows in to a single row with different columns. Observe the icon, it says that will convert rows to column.
- Non-Pivot columns : List of columns specified here by you, those will be displayed as it is in the target
- Pivoted columns: These columns containing data which you want to whirl.
- Pivot axis columns: Based on this seq and unique axis value, columns will be created.
- Duplicate value: Suppose, your source data-set contains duplicate values, then you can fetch first row, last row or you can abort the process.
- Axis value: This represents a particular set of columns.
- Column prefix: You can prefix column names for the rotated data.
- Input data is grouped: If this option is checked, then RPivot reckons that the set of data which is coming from the source is sorted and grouped, read the data, then find the appropriate value for column prefix and then process the data.
Note: Sometimes, our record-set doesn’t contains axis value. For those scenarios, we need to compute a rank for every group.
Design Steps: I’m using (target table of pivot transform example) as a source which has (SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA) 4 columns.
- Drag the source table from Datastore object library on to the workspace, drag the Reverse-Pivot transform and place after your source.
- Now place a QRY step next to RP transform, after that place a target table object and now connect it each object.
- Click on RP transform, drag SNAME to non-pivotal column list. Drag PIVOT_HDR, PIVOT_DATA to pivoted columns list and provide default values as NULL.
- In the Output columns sections, select PIVOT_SEQ as pivot axis column and select Duplicate value as First row/Last row(if you have any duplicate values)
- then add Axis values and Column prefix to represent a particular set of data.
- Now, save the definition, you can able to see the out put columns in RP transform out schema.
- Come back to DF designer window click on QRY instance, drag the required coulmns onto out schema. Here I renamed all pivoted data columns to JAN_SAL, FEB_SAL, MAR_SAL.
- Save the DF definition, validate it and execute it. Check the resultant dataset.