How to Convert Rows into Columns in Informatica? | Using Expression Transformation [with an Example]

Most of the time interviewer asks questions to ETL Informatica developers like as

• Which transformation converts rows to columns in Informatica?
• Which transformation can be used to transpose rows to columns?

– There are two ways to use the Expression transformation or Normalizer transformation.

Here, I am converting rows into columns in Informatica by using the Expression transformation.

Let’s see the example,

Convert or Transpose Rows into Columns in Informatica

Source: Students_records [ as Flat File]

Student_Id	Student_Name	Subject
0001	Ram	English
0001	Ram	Math
0001	Ram	Hindi

Target: Transpose_students_records [ as Flat File]

Student_Id	Student_Name	Subject_1	Subject_2	Subject_3
0001	Ram	English	Math	Hindi

We can solve this example with the help of the below transformations.

1. Sorter transformation
2. Expression transformation
3. Aggregator transformation

Informatica Designer

In Informatica Power Center Designer, the mapping looks like this.

Let’s describe each transformation one by one.

Sorter Transformation

In Sorter transformation, use Key to sort the data based on Student_name in Ascending order.

Expression Transformation

In the Expression transformation,

• The first step is to create the v_flag for generating the sequence.

v_flag= v_flag+1

• After that create the new columns for different subjects.

v_Subject_1= IIF (v_flag=1, Subject_1, v_Subject_1)
v_Subject_2= IIF (v_flag=2, Subject_2, v_Subject_2)
v_Subject_3= IIF (v_flag=3, Subject_3, v_Subject_3)
o_Subject_1=v_Subject_1
o_Subject_2=v_Subject_2
o_Subject_3=v_Subject_3

Aggregator Transformation

In Aggregator transformation, GroupBy is applied on Student_Id.

In such a way, mapping is created in Informatica Designer. Make sure to validate mapping.

Mapping [m_transpose_rows_colunms]

Workflow Manager

In Informatica Power Center workflow manager, create session tasks based on the mapping created. Configure source and target parameters in session properties.

This is one of many ways we can transpose rows to columns with expression transformation. In an upcoming article, I will transpose columns to rows with the help of another Informatica transformation.

Please let us know your thoughts in the comment below.

Read more related Informatica Interview Questions:

• Active transformation vs Passive transformation in Informatica
• Filter transformation vs Router transformations in Informatica
• Source qualifier transformation vs Joiner transformation in Informatica
• Joiner transformation vs Lookup transformation in Informatica

Thanks for Reading!