1. Convert rows in to columns?
Source I/P Table:
ID
|
NAME
|
MONTH
|
SALES
|
1
|
A
|
JAN
|
100
|
1
|
A
|
FEB
|
120
|
1
|
A
|
MAR
|
135
|
2
|
B
|
JAN
|
110
|
2
|
B
|
FEB
|
130
|
2
|
B
|
MAR
|
120
|
Expected Target O/P:
ID
|
NAME
|
JAN
|
FEB
|
MAR
|
1
|
A
|
100
|
120
|
135
|
2
|
B
|
110
|
130
|
120
|
Follow the below Steps to get the Required output
I am using Informatica 8.6 Version and Oracle as DB
1. Create the above given Source and Target tables in your database.
2. In mapping Designer Windows Drag and Drop the Source and Targets
3. Creating a Mapping with a name
4. The overall Mapping is looks like below figure(1)
5. Now Create an Expression transformation
6. Connect the ID,NAMEdouble click Expression transformation and add 3-Output ports:
6. in output ports created as above write the following code for each output port:
O_JAN:- IIF(MONTH='jan', AMOUNT, NULL)
O_FEB:- IIF(MONTH='feb', AMOUNT, NULL)
O_MAR:- IIF(MONTH='mar', AMOUNT, NULL)
-->Apply -->Ok
7. Now Create an Aggreagator transformation for calculating the aggregate values as follows and link the ports from Expression Transformation(ID, NAME, O_JAN, O_FEB, O_MAR) and make them as Input Ports only.
8. Double click the Aggregator T/R and Create 3-O/P ports as shown in below:
9. The output ports are 1. OUT_JANUARY 2. OUT_FEBRUARY 3. OUT_MARCH
Code for the above create output ports
OUT_JANUARY:- MAX(O_JAN)
OUT_FEBRUARY:- MAX(O_FEB)
OUT_MARCH:- MAX(O_MAR)
Click Apply and Click ok
Connect the ports(ID, NAME, OUT_JANUARY, OUT_FEBRUARY, OUT_MARCH) to the Target Table..
Create a Session and Workflow and Run it.. Works Fine:
The Sample input is like this:
The Sample Output is like this:
