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
Expanding record based on Key
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
-
- Premium Member
- Posts: 306
- Joined: Wed Jun 21, 2006 11:41 am
-
- Participant
- Posts: 152
- Joined: Mon Mar 07, 2011 6:16 am
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.
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.
Soumya