ODI PIVOT-COLUMNS TO ROWS

 Hi Gurus,

In "Informatica" we have "Normalizer transformation" and in "Datastage" and "OWB" we have PIVOT stage and "PIVOT Rule" to convert "COLUMNS TO ROWS"

This tutorial will guide you how to convert "COLUMNS TO ROWS" using ODI.

I have a table T with the following structure and Data

 And i have a table TT with the following structure

Now i want to do mapping between T and TT
 to get the following output data in TT

To do this using ODI
Open the Designer
Expand the project
Right click on interfaces
And click on new interface
Give the name as INF_TT

 Drag and drop T from the SRC_SCOTT model on to the source pane

Drag and Drop TT from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping
enter the string value 'COLUMN1' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN1" column of T data store on VAL column of TT table
Now click on add new data set

Click on + symbol two time to create two new data sets of "UNION" relation as shown below

Click on <New Dataset1>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
perform automatic mapping by clicking on yes
enter the string value 'COLUMN2' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN2" column of T data store on VAL column of TT table

Click on <New Dataset2>
Now Drag and drop the T table from SRC_SCOTT model to the source pane
Perform automatic mapping by clicking on yes
enter the string value 'COLUMN3' for the mapping properties of target column SOURCE and select execute on staging area
Also drag drop the "COLUMN3" column of T data store on VAL column of TT table

 Click on FLOW tab

Click on Target to see the IKM target properties
Set the "flow_control" option to false
Set the "Truncate" option to true
Click on save
Click on execute

Go to operator log
Check the log for this session
Open it and check the number of inserts

Thanks & Regards
WWW.ODIGURUS.COM