How to convert rows into cols using ODI 11g? (which is pivot in ODI 12c)

Scripts we are going to use in this demo:

--Source Table1 CREATE TABLE COMPANY_BRANCHES (BRANCH_CODE VARCHAR2(3), BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100)) --Source Table2 CREATE TABLE COMPANY_STATS (BRANCH_CODE VARCHAR2(3), YEAR VARCHAR2(4), QUARTER VARCHAR2(2), PROFIT_MILLIONS NUMBER(8,2)) --Target Table CREATE TABLE COMPANY_ANALYSIS (BRANCH_NAME VARCHAR2(20), ADDRESS VARCHAR2(100), YEAR VARCHAR2(4), QUARTER1_MILLIONS NUMBER(8,2), QUARTER2_MILLIONS NUMBER(8,2), QUARTER3_MILLIONS NUMBER(8,2), QUARTER4_MILLIONS NUMBER(8,2) ) --Source Table1 Data INSERT INTO COMPANY_BRANCHES VALUES('NLR','NELLORE','ARAVINDANAGAR'); INSERT INTO COMPANY_BRANCHES VALUES('GDR','GUDUR','EAST STREET'); --Source Table2 Data INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q1',0.8); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q2',1.2); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q3',1.5); INSERT INTO COMPANY_STATS VALUES('NLR','2015','Q4',0.75); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q1',0.9); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q2',1.35); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q3',1.4); INSERT INTO COMPANY_STATS VALUES('NLR','2016','Q4',0.82); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q1',0.6); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q2',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q3',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2015','Q4',0.8); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q1',1.0); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q2',1.3); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q3',1.32); INSERT INTO COMPANY_STATS VALUES('GDR','2016','Q4',0.78);

Approach:

Let us consider source data store and target data store are available in ODI Model.

Step 1:

Create new interface and drag & drop 2 source datastore into source editor and drag and drop 1 target datastore into target editor.

Step 2:

Create join between two sources as follows

COMPANY_BRANCHES.BRANCH_CODE=COMPANY_STATS.BRANCH_CODE

Step 3:

Map columns as follows:

Target Datastore Columns	Source Datastore Columns
BRANCH_NAME	COMPANY_BRANCHES. BRANCH_NAME
ADDRESS	COMPANY_BRANCHES. ADDRESS
YEAR	COMPANY_STATS.YEAR
QUARTER1_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q1',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER2_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q2',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER3_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q3',COMPANY_STATS.PROFIT_MILLIONS,NULL))
QUARTER4_MILLIONS	MAX(DECODE(COMPANY_STATS.QUARTER,'Q4',COMPANY_STATS.PROFIT_MILLIONS,NULL))

Explanation:

We need to group all 

QUARTER1_MILLIONS,

QUARTER2_MILLIONS,

QUARTER3_MILLIONS,

QUARTER4_MILLIONS

based on BRANCH_NAME, ADDRESS, YEAR so that we need MAX

If we maps the columns like this in ODI then it internally group by following columns

GROUP BY 

COMPANY_BRANCHES.BRANCH_NAME,

COMPANY_BRANCHES.ADDRESS,

COMPANY_STATS.YEAR

Select appropriate KM's.

Output: