pivoting the data

[dsdeveloper66]

Hi ,

Below is the sample i/p and o/p data that i need to design the data.

I/P1
column1|column2|column3|column4
A 50 51

I/P2
column1|column2|column3|column4
A 1 20 50
A 2 21 50
A 3 22 50
A 1 23 51
A 2 24 51
I/P2

O/P
column1|column2|column3|column4
A 1 20 23
A 2 21 24
A 3 22 ''

NEED TO JOIN TWO I/PS WITH COLUMN1 OF I/P1 AND COLUM2,COLUMN3 OF I/P 1

I tried to design using the join b/w the i/p1 and i/p2 but could not get the o/p as expected.

Let me know if you have any Questions in understanding.

Thanks in advance

[Minhajuddin]

Assuming that you have a maximum of two records with the same values for col1 and col2 in IP1, and also assuming that the order of col3, col4 doesn't matter.

You can do it using the following design

Code: Select all

IP_FILE1====>copy============
		|            |
		|            |
		|            |
		V            V
IP_FILE2====>Join==========>Join=======>Pivoting logic
              (ip1.col2=ip2.col4)  (ip1.col3=ip2.col4)