I have a file with the below layout and sample data
PARTY_ID
CAP_SORT_CODE
CAP_ACC_NO
MONTH_NO
CCAF_EXTRNL_STAT_CODE
1347736
309911
1038471
1
9
1347736
309911
1038471
2
9
1347736
309911
1038471
3
9
1448934
874415
71778768
1
9
1448934
874415
71778768
2
9
1448934
874415
71778768
3
9
1818985
309366
15679868
1
9
1818985
309366
15679868
2
9
1818985
309366
15679868
3
9
2349634
309576
51215260
1
9
2349634
309576
51215260
2
9
2349634
309576
51215260
3
9
2656042
309633
644240
1
9
2656042
309633
644240
2
9
2656042
309633
644240
3
9
2884319
309576
17374768
1
9
2884319
309576
17374768
2
9
2884319
309576
17374768
3
9
3513240
302582
176317
1
9
3513240
302582
176317
2
9
3513240
302582
176317
3
9
4497094
302586
718161
1
9
4497094
302586
718161
2
9
4497094
302586
718161
3
9
5634459
309141
628518
1
9
5634459
309141
628518
2
9
5634459
309141
628518
3
9
5681443
309367
273287
1
9
5681443
309367
273287
2
9
5681443
309367
273287
3
9
6707092
309018
1096728
1
9
It is sorted on
PARTY_ID
CAP_SORT_CODE
CAP_ACC_NO
MONTH_NO
Now, what I want is - it will look at the values Month_no, based on the max value of this variable, it will define the length of the array. Then for a specific combination of party_id, cap_sort_code and cap_acc_no, if the value of Month_no is 001, then it will load the value of CCAF_EXTRNL_STAT_CODE to CCAF_EXTRNL_STAT_CODE1, then come to the next record, see if the month_no = 002 for the same party_id, cap_sort_code and cap_acc_no combination, then it will move the value of CCAF_EXTRNL_STAT_CODE to CCAF_EXTRNL_STAT_CODE2 and it will go on doing this until it reaches a record where party_id, cap_sort_code and cap_acc_no changes. Once these values are moved to the array specific variables, it will finally delete all the extra records for this party_id, cap_sort_code and cap_acc_no and keeping only one record with all array variables having values. So, the table is below is what I am looking for.
PARTY_ID
CAP_SORT_CODE
CAP_ACC_NO
MONTH_NO
CCAF_EXTRNL_STAT_CODE1
CCAF_EXTRNL_STAT_CODE2
CCAF_EXTRNL_STAT_CODE3
1347736
309911
1038471
1
9
9
9
1448934
874415
71778768
1
9
9
9
1818985
309366
15679868
1
9
9
9
2349634
309576
51215260
1
9
9
9
2656042
309633
644240
1
9
9
9
2884319
309576
17374768
1
9
9
9
3513240
302582
176317
1
9
9
9
4497094
302586
718161
1
9
9
9
5634459
309141
628518
1
9
9
9
5681443
309367
273287
1
9
9
9
6707092
309018
1096728
1
9
I have just demonstrated this above example only for sample records where Month_no is 1,2,3 (it can go till 36). Once the final table is created, Month_no to be dropped.
Need this help urgently.
... View more