Expanding record based on Key

[JPalatianos]

Hi,
I have an input table with 2 columns: ITEM_ID and TXN_TYPE with values for example as follows:
ITEM_ID TXN_TYPE
1 A
1 B
2 A
3 A
3 B
3 C

I want to convert this so that there is one row per ITEM_ID, with columns for each occurrence from the original table. For example:
ITEM_ID TXN_TYPE1 TXN_TYPE2 TXN_TYPE3
1 A B
2 A
3 A B C

Is this easily doable in datastage?
Thanks -- John

[JPalatianos]

Hi,
Can someone move this to the enterprise forum. We are running 8.0.1 enterprise edition at our shop.
Thanks - - John

[chulett]

I certainly can if you are looking for a PX answer, but you marked it for Server and if that's the solution you are looking for we're in the right place.

[kab123]

Do a Lookup with same table and map the column if the values only if found and then write the output to a hash file. If you really care for the order of the columns then you may have to do a sort later and then write to the output hash file.

[JPalatianos]

Sorry...I am looking for a parallel answer. I accidentally left the default of "Server".
Thanks - - John

[chulett]

Moved and updated.

[soumya5891]

Considering one thing that TXN_TYPE is either A or B or C

1. Use a filter stage to separate out three links l1,l2,l3,l4(say)
2. In l1 use TXN_TYPE="A" and rename the col TXN_TYPE as TXN_TYPE_1
3. In l2 use TXN_TYPE="B" and rename the col TXN_TYPE as TXN_TYPE_2
4. In l3 use TXN_TYPE="C" and rename the col TXN_TYPE as TXN_TYPE_3
5. In l4 use TXN_TYPE="A" or TXN_TYPE="B" or TXN_TYPE="C".And then perform a remove duplicate on ITEM_ID.
6. Now perform a left outer join between 5,2,3,4,keeping 5 as left link.

Hope you will get your desired output.Just try and let me know in case of any concern.