PIVOT in ODI 12c

How to Implement PIVOT component in ODI 12c?

Scenario:

To avoid redundant data

Scripts:

CREATE TABLE STUDENT_LOC(

SNAME VARCHAR2(30),

PREFERENCE VARCHAR2(20),

LOCATION VARCHAR2(20));

INSERT INTO STUDENT_LOC VALUES('TRINESH','LOCATION1','HYDERABAD');

INSERT INTO STUDENT_LOC VALUES('TRINESH','LOCATION2','BANGALORE');

INSERT INTO STUDENT_LOC VALUES('SHANTHI','LOCATION1','HYDERABAD');

INSERT INTO STUDENT_LOC VALUES('SHANTHI','LOCATION2','BANGALORE');

INSERT INTO STUDENT_LOC VALUES('BAPI','LOCATION1','HYDERABAD');

INSERT INTO STUDENT_LOC VALUES('BAPI','LOCATION2','USA');

INSERT INTO STUDENT_LOC VALUES('RAJU','LOCATION1','USA');

INSERT INTO STUDENT_LOC VALUES('RAJU','LOCATION2','BANGALORE');

INSERT INTO STUDENT_LOC VALUES('DEEPAK','LOCATION1','DUBAI');

INSERT INTO STUDENT_LOC VALUES('DEEPAK','LOCATION2','HYDERABAD');

COMMIT;

CREATE TABLE STUDENT_LOCATION (

SNAME VARCHAR2(30),

LOCATION1 VARCHAR2(20),

LOCATION2 VARCHAR2(20));

/

Steps to Implement:

1.     Create new Mapping

2.     Drag and drop source datastore(STUDENT_LOC) and target datastore from model(STUDENT_LOCATION )

3.     Drag and drop Pivot component from component palette into mapping editor

4.     Connect output connector of PIVOT to input connector of Target datastore

It will pop up as below:

Enable checkbox “Create Attributes On Source” and “Auto Map” and click on OK.

5.     Connect output connector of source datastore to input connector of Pivot Component

6.     Disable checkbox “Create Attributes on Target”

7.     Select Pivot component and go to property inspector

8.     For Sname under expression select “Sname” from source datastore

Row Locator: Select “Preference” Column from source Datastore

Click ok “+” symbol to create ROW Locator values

“LOCATION1”

“LOCATION2”

Under “Matching row” for LOCATION1 column in pivot component select “LOCATION1” and for expression select “Location” Column

Under “Matching row” for LOCATION2 column in pivot component select “LOCATION2” and for expression select “Location” Column

It looks as below:

 

9.     Select appropriate knowledge module in physical tab by selecting target datastore (IKM Oracle insert.Global by default it will comes, you can leave as it)

10. Execute Mapping

Output & Flow: