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 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 R Pivot 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.
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 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 R Pivot 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
Data store object library on to the work space,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.
(SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA) 4 columns. Drag the source table from
Data store object library on to the work space,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.
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
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 columns into 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.
