Scenario 5: How to Convert Column To Row (1)

In this section we have several scenario based upon source data and expected output. We will explore it one by one. 

Scenario 5: How to Convert Column To Row

First Challange

(Source)

TERM,JAVA,.NET,ORACLE

1st,60,40,70

2nd,50,65,80

(Target)

TERM,JAVA,.NET,ORACLE

1ST,60,null,null

1ST,60,40,null

1ST,60,40,70

2nd,50,null,null

2nd,50,65,null

2nd,50,65,80

Note: Null is just a representation. Null means we don't have to insert value in that column.

Theory/Analysis

After analyzing source and target data we can conclude that we have to convert subject JAVA three times; .NET two times and ORACLE only once.For Converting Columns to Row we need Normalizer Transfromation and we have to be little tricky while passing Source rows to Normalizer port.

Practically

Step 1: Drop your Source in Mapping Designer

Step 2:

Expression 

Create three dummy port

Dummy (Output Port) NULL

Dummy1 (Output Port) NULL

Dummy2 (Output Port) NULL

Step 3:

Normalizer

Create Normalizer Port in Normalizer Tab

PORT_NAME       DATA_TYPE      OCCURENCE

Term                      String                       0

Java                       Number                    3

Dot_Net                Number                    3

Oracle                   Number                    3

Now connect port from Expression to Normalizer (see image below) and Normalizer to Target

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