Tuesday 2 September 2014

How to convert single row from source to three rows in target

I have a source table called Subjects as below
Source:
Subject1   Subject2   Subject3
Telugu      Hindi         English
Maths       Social        Science

and I want to all those Subject1, Subject2 and Subject 3 into a single column of the Target table:

Target:
Subjects
Telugu
Hindi
English
Maths
Social
Science

Approach 1 using Normalizer:

Note: Create a source and target definition as columns names defined above.


After Importing Source and Target definition into designer workspace. Add Normalizer transformation to the mapping.
From the properties of the Normalizer transformation -- go to Normalizer tab then add the port (column) and name it as Subjects then choose Occurs as 3. Which mean it will loop the single record 3 times with for every single column.
 Then mapping the output Subject column to the target instance as below


Approach 2 using Union:
After Importing Source and Target definition into designer workspace.
Added Union Transformation to the mapping. Select the Union Transformation and right click and edit. 
Go to the Groups tab. (For Union transformation we need to create required no groups 1st before added ports) as below
After creating groups, go to Group Ports tab to create required no of ports/columns as below
 
 Then mapping each subject port/column to port of each group of union transformation as below and map output port(s)/column(s) to the target instance.


Approach 3 using Router, but it will create 3 different target file for each subject (this approach is worst one):
After Importing Source and 3 Target definition of the same target  into designer workspace because we are using router in this process.
Add Router transformation to the mapping as below. Select Router, right click and edit.
Go to Groups tab to create groups for each subject because we are converting each single records into multiple, in other words we are converting each subject as single record in the target instance.
For group filter condition as "TRUE" as above.
Map the Source Qualifier ports with Router ports as below
 
From Router map Subject1 from Subject1 Group to Target instance 1, Subject2 from Subject2 Group to Target instance 2 and Subject3 from Subject3 Group to Target instance 3.
In this process we will be getting 3 target instance for each column/subject because of router.

1 comment:

  1. Strange "water hack" burns 2lbs overnight

    Over 160 000 women and men are utilizing a simple and secret "liquids hack" to drop 2lbs each and every night in their sleep.

    It is painless and works with anybody.

    Here's how to do it yourself:

    1) Go grab a drinking glass and fill it half full

    2) And then use this proven hack

    and you'll become 2lbs skinnier the very next day!

    ReplyDelete