ODI PIVOT- ROWS TO COLUMNS

 Hi Gurus,

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

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

Let us consider the following two source table structures TABLE1 and TABLE2
 and target table structure TABLE3

Now we need to map the target table TABLE3 with source tables TABLE1 and TABLE2

To do this

Open the Designer
Expand the project
Right click on interfaces
And click on new interface
Give the name as INF_TABLE3
Click on mapping tab

 Drag and drop TABLE1 and TABLE2 from the SRC_SCOTT model on to the source pane
Drag the LOCID column from TABLE1 table and drop on LOCID column of TABLE2 table , then it will create a join. click on join then you will see the JOIN properties below
Drag and Drop TABLE3 from TRG_SCOTT model on to the target pane
Click ok to perform Automatic mapping

 Edit the mapping properties for the following columns and enter the given expression
 SERVICE_STARTS_MON:
MAX(DECODE(TABLE2.DAY_OF_WEEK,0,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_TUE:
MAX(DECODE(TABLE2.DAY_OF_WEEK,1,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_WED:
MAX(DECODE(TABLE2.DAY_OF_WEEK,2,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_THU:
MAX(DECODE(TABLE2.DAY_OF_WEEK,3,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_FRI:
MAX(DECODE(TABLE2.DAY_OF_WEEK,4,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_SAT:
MAX(DECODE(TABLE2.DAY_OF_WEEK,5,TABLE2.SERVICE_STARTS,NULL))
SERVICE_STARTS_SUN:
MAX(DECODE(TABLE2.DAY_OF_WEEK,6,TABLE2.SERVICE_STARTS,NULL))

 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