Problem in Joining

[BVAP]

Hi Everybody,
Iam trying to Have a join in between two tables based on a common field(Say CUST_NO).
I want to pick up Other field (Say ITEM) in Table 2 based on the Key between these two tables. But the problem is the Common field (CUST_NO) in the second table is not Unique. So when i try to test this join for one CUST_No say 123, i get one record from Table 1 and 2 from Table two resulting in 2 records after Join. If i observe these 2 records all the values are same except the ITEM. My requirement is to have a single record With both these ITEMS under field ITEM1 and ITEM 2 in my output.
Or is there any other way to get a single record with the values of these two Items in the same record.

Thanks In adv..

[sb_akarmarkar]

Hi,

You need to 1st sort CUSTNO then using stage variable you need to store CUSTNO and ITEM , and Compare old CUSTNO with current CUSTNO if it is equal then map old ITEM to ITEM1 and new to ITEM2 in tarnsformer ,use aggregator berfore target in that say MAX(ITEM1) and MAX(ITEM2) group by other columns..I think this may help you in solving problem..

Thanks,
Anupam

[ray.wurlod]

You need to apply a vertical pivot to the joined data (which contains multiple rows per CUSTNO). Search the forum for vertical pivot; there are several techniques available. Pivot stage is not one of them.