How to implement un-pivot concept in ODI 11g

SQL Scripts used in Demo:

CREATE TABLE SALES( YEAR          VARCHAR2(4) , Q1      NUMBER(10,2) , Q2     NUMBER(10,2) , Q3      NUMBER(10,2) , Q4      NUMBER(10,2) ); / CREATE TABLE SALES_PER_QUARTER( YEAR VARCHAR2(4), QUARTER VARCHAR2(2), SALES NUMBER(10,2)); / INSERT INTO SALES VALUES('2014',23345.32,32456.41,21234.21,18324.12); INSERT INTO SALES VALUES('2015',24324.12,34234.51,25345.18,17234.86); COMMIT; /

Scenario

In this scenario, we are converting rows into columns.

Implementing un-pivot component which is available in ODI 12c (which will be available from 12.1.3 version onwards) in ODI 11g

Process:

1.      Create model

2.      Create Reverse engineer above mention datastores

3.      Create new interface

4.      Drag and drop Source datastore (SALES) and Target Datastore (SALES_PER_QUARTER)

As mention below and map accordingly.

5.      Click on “highlighted” dataset symbol (in the above diagram) which will pop-up for datasets creation.

6.      Create four datasets for four quarters as below.

7.      Now interface looks as follows

8.      For Quarter2 –Quarter Column ‘Q2’ and Sales Column <Q2_Value>

Similarly we need to repeat for Quarter3 and Quarter4

9.      Make sure that Quarter mapping should be “Staging”

10.  Select appropriate knowledge as per your requirement (In this case I am selecting IKM SQL Control Append) and flow control as “false” (because I don’t have any key on target side)

11.  Execute it.

Output: