Scenario 13 A: How to Convert Row to Column

Scenario 13 A: How to Convert Row to Column

How to achieve target below...

(Source)

SUBJECT,MARKS
JAVA,60
ORACLE,50
INFORMATICA,78
JAVA,45
ORACLE,55
INFORMATICA,52

(Target)
Column1  Column2   Column3
JAVA ORACLE    INFORMATICA
60             50             78
JAVA ORACLE    INFORMATICA
45              55             52

Do, you wan't to write it into single column? No issue I have covered both, for single column you have to skip step 5.

Theory/Analysis
After analyzing source and target we can conclude that first we have to convert row to column then column to row. Confused.... What are you talking about first first row to column then column to row....
Wait wait... Let me help you.
First Row to Column: This we have to achieve because JAVA, ORACLE, INFORMATICA. Is in't it converted from row to column. Agreed? OK..
Second Column to Row: This we have to do because in Source we have two columns and second column data is coming to second row.
Hope it's clear by now. Let's do it Practically

Practically

Step 1:
Drag and Drop your source and target to Mapping Designer.

Step 2:
Expression
Create 10 Ports

ID (Variable Port) = 1

NEW_ID (Variable Port) = IIF(OLD_ID=3, ID,  OLD_ID+1)

OLD_ID (Variable Port) = NEW_ID

SUBJECT_CONCAT (Variable Port) = IIF(NEW_ID = 1 OR ISNULL(OLD_SUBJECT), SUBJECT, OLD_SUBJECT||','||SUBJECT)

OLD_SUBJECT (Variable Port) = SUBJECT_CONCAT

MARKS_CONCAT (Variable Port) = IIF(NEW_ID =1 OR ISNULL(OLD_MARKS), MARKS,OLD_MARKS||','||MARKS)

OLD_MARKS (Variable Port) = MARKS_CONCAT

O_NEW_ID (Output Port) = NEW_ID

O_SUBJECT (Output Port) = SUBJECT_CONCAT

O_MARKS (Output Port) = MARKS_CONCAT

Step 3
Filter
Filter Condition = O_NEW_ID=3

Step 4
Normalizer
Create one Column in Normalizer Tab

Column Name  Occurs    Data Type   Prec
COLUMN1        2                String          100

Step 5
(Unnecessary Step, Your choice, If you want to skip this because you want to load data in single column target go ahead connect COLUMN1 port from Normalizer to target)
I am using this step for better understanding.. Split column value in three different column based upon comma.
Expression
Create three ports
COL1 (Output Port) =SUBSTR(COLUMN1,1,INSTR(COLUMN1, ',',1, 1)-1)

COL2 (Output Port) =SUBSTR(COLUMN1, (INSTR(COLUMN1, ',',1, 1)+1),  ((INSTR(COLUMN1, ',',1, 2)- INSTR(COLUMN1, ',',1, 1)-1)))

COL3 (Output Port) =SUBSTR(COLUMN1, (INSTR(COLUMN1, ',',1, 2)+1))

Step 6: 
Excell Target: At session level for target file properties have extension in .CSV and enjoy your excell target. What you said? It's not excell target. Ok!! Give extension .xls but here you will receive an error while opening your target file. Ignore that and proceed to see the data and. If you are giving .xlsx then you won't be able to open it at all. It will throw error file has been corrupted or not in proper format.
Conclusion: Informatica doesn't create excell target. To be safer side what we are doing in real time create target in .csv format and have script to convert it to excell file with .xls or .xlsx extension.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com