Scenario 20: How to insert same group of data in single column.

Scenario 20: How to insert same group of data in single column.

Source
ID,NAME,DEPTNO
100,JOHN,10
200,WALTER,20
300,GIBSON,30
400,KERRY,20
500,PHILIP,10

Target
DEPT10,DEPT20,DEPT30
JOHN,WALTER,GIBSON
PHILIP,KERRY,

Theory: It's quite challenging.... Hmmmm For achieving target above we need to treat each unique deptno as one column then we need to join all three columns to create a row.

Practically:

Step 1: Drag and drop your source and target to mapping designer.

Step 2:
Router
Create Three groups in Router
DEPT10 : DEPTNO=10
DEPT20 : DEPTNO=20
DEPT30 : DEPTNO=30

Step 3:
Expression
Create three Expression Transformation and connect each group of Router to one Expression Transformation. Let's say... EXPRESSION 1, EXPRESSION 2, EXPRESSION 3

Now, we need to crate an ID for each and every record based upon which we will join these columns and create a row.

EXPRESSION 1

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) =  ID

EXPRESSION 2

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

EXPRESSION 3

ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output  Port) =  ID

Now we have three pipeline which we need to join so, (n-1)2 (Where n=pipeline) = 2 Joiner Transformation Required
As we are joining pipelines which are originated from same source qualifier so as per the rule we need to pass sorted input for both Joiner Transformation. So, let's create Sorter First

Step 4
Sorter
Connect Name and OUT_ID column from EXPRESSION 2 to Sorter.

OUT_ID (Key Port)

Step 5:
Joiner
Connect Name and OUT_ID from expression 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 6
Sorter
Connect Name and OUT_ID column from EXPRESSION 3 to sorter.

OUT_ID (Key Port)

Step 7:
Joiner
Connect Name and OUT_ID from Joiner 1 and Sorter
Joiner Properties needs to be set in Properties Tab and Condition Tab
Sorted Input (Checked)
Join Type: Full Outer
Join Condition= OUT_ID=OUT_ID

Step 8
Now you will have total three name column will be there in your Joiner Transformation. Connect each name to target column.

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