Loop example: converting a single row to multiple rows (DataStage®)

Last updated: Mar 12, 2025
Loop in DataStage example: converting a single row to multiple rows

You can use the Transformer stage to convert a single row for data with repeating columns to multiple output rows.

Input data with multiple repeating columns

When the input data contains rows with multiple columns containing repeating data, you can use the Transformer stage to produce multiple output rows: one for each of the repeating columns.

For example, if the input row contained the following data.
Col1 Col2 Name1 Name2 Name3
abc def Jim Bob Tom
You can use the Transformer stage to flatten the input data and create multiple output rows for each input row. The data now comprises the following columns.
Col1 Col2 Name
abc def Jim
abc def Bob
abc def Tom
To implement this scenario in the Transformer stage, make the following settings:
Loop condition
Enter the following expression as the loop condition.
@ITERATION <= 3
Because each input row has three columns containing names, you need to process each input row three times and create three separate output rows.
Loop variable
Define a loop variable to supply the value for the new column Name in your output rows. The value of LoopVar1 is set by the following expression:
IF (@ITERATION = 1) THEN inlink.Name1
ELSE IF (@ITERATION = 2) THEN inlink.Name2
ELSE inlink.Name3
Output link metadata and derivations
Define the output link columns and their derivations:
  • Col1 - inlink.col1
  • Col2 - inlink.col2
  • Name - LoopVar1