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

by

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_IdStudent_NameSubject
0001RamEnglish
0001RamMath
0001RamHindi

Target: Transpose_students_records [ as Flat File]

Student_IdStudent_NameSubject_1Subject_2Subject_3
0001RamEnglishMathHindi

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:

Thanks for Reading!

Are you looking for job?

Job openings »

 

Leave a Comment