Suppose in our Source Table we have data as given below:

Suppose in our Source Table we have data as given below:

Student Name	Subject Name	Marks
Sam	Maths	100
Tom	Maths	80
Sam	Physical Science	80
John	Maths	75
Sam	Life Science	70
John	Life Science	100
John	Physical Science	85
Tom	Life Science	100
Tom	Physical Science	85

We want to load our Target Table as:

Student Name	Maths	Life Science	Physical Science
Sam	100	70	80
John	75	100	85
Tom	80	100	85

Describe your approach.

Ans.
Here our scenario is to convert many rows to one rows, and the transformation which will help us to achieve this is Aggregator .Our Mapping will look like this:

Mapping using sorter and Aggregator

We will sort the source data based on STUDENT_NAME ascending followed by SUBJECT ascending.

Sorter Transformation

Now based on STUDENT_NAME in GROUP BY clause the following output subject columns are populated as
MATHS: MAX(MARKS, SUBJECT='Maths')
LIFE_SC: MAX(MARKS, SUBJECT='Life Science')
PHY_SC: MAX(MARKS, SUBJECT='Physical Science')

Aggregator Transformation