Reverse Pivot in BODS

 Reverse Pivot Transform in BODS:

This transform converts rows into columns. It will group the dataset of different rows into 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 swap.
• Pivot axis columns: Based on this seq and unique axis value, columns will be created.
• Duplicate value: Suppose, your source dataset 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,  it reads the data, then it finds the appropriate value for column prefix and processes the data.

Note: Sometimes, the record-set doesn’t contain axis value. For those scenarios, you need to compute a rank for every group.

Design Steps:

I’m using target table of pivot transform example discussed in previous post as a source. This has SNAME, PIVOT_SEQ, PIVOT_HDR, PIVOT_DATA as 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 Query step next to RP transform, after that place a target table object and then connect 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.

• Save the definition, you can able to see the output columns in RP transform schema out.
• Come back to Dataflow designer window, click on Query instance, drag the required columns into schema out. Here, I renamed all pivoted data columns to JAN_SAL, FEB_SAL, MAR_SAL-

• Save the Dataflow definition, validate and execute it. Check the resultant dataset-