How to convert a single row into multiple rows ?

Below is a screenshot of our input data

City	State	Name1	Name2	Name3
xy	FGH	Sam	Dean	Winchester

We are going to read the above data from a sequential file and transform it to look like this

City	State	Name
xy	FGH	Sam
xy	FGH	Dean
xy	FGH	Winchester

So lets get to the job design

Step 1: Read the input data

Step 2: Logic for Looping in Transformer

In the adjacent image you can see a new box called Loop Condition. This where we are going to control the loop variables.

Below is the screenshot when we expand the Loop Condition box

The Loop While constraint is used to implement a functionality similar to “WHILE” statement in programming. So, similar to a while statement need to have a condition to identify how many times the loop is supposed to be executed.

To achieve this @ITERATION system variable was introduced. In our example we need to loop the data 3 times to get the column data onto subsequent rows.

So lets have @ITERATION <=3

Now create a new Loop variable with the name LoopName 

The derivation for this loop variable should be

If @ITERATION=1 Then DSLink2.Name1 Else If @ITERATION=2 Then DSLink2.Name2 Else DSLink2.Name3

Below is a screenshot illustrating the same

Now all we have to do is map this Loop variable LoopName to our output column Name

Lets map the output to a sequential file stage and see if the output is a desired.

After running the job, we did a view data on the output stage and here is the data as desired.

Making some tweaks to the above design we can implement things like

• Adding new rows to existing rows
• Splitting data in a single column to multiple rows and many more such stuff..