Hi. This is no easy task for me, but I'm sure extremely easy for some here.
I have a Table_A at the account level. Each row has a whole bunch of message codes, some can have 20, some 30 or even more. And for one account msg_1 can = 245 and in another acct msg_1 can = 567... and so on.
In another Table_B, I have a list of the same codes but they're located vertical. I want to match the accounts in Table_A with the accounts in Table_B and create a new column name as the message number M245 and M567, etc.. and either a 1 or a 0 if that account has it or not. Below is a better visual:
Table_A
Acct_no msg_1 msg_2 msg_3 msg_4
001 1 12 999 956
002 2 23 49 999
003 12 53 3 378
Table_B
CODE_ID Status Notes
msg_1 Decline ABC
msg_2 Decline CDE
msg_3 Approve HET
........
msg_49 Decline HDG
........
msg_999 Approve JDT
NEED Table_C
Acct_no msg_1 msg_2 msg_3 msg_4 Dec_msg_1 Dec_msg_2 App_msg_3 ...... Dec_msg_49 ...... Appr_msg_999
001 1 12 999 956 1 0 0 0 1
002 2 23 49 999 0 1 0 1 1
003 12 53 3 378 0 0 1 0 0
The Dec_ and App_ in the field name in Table_C is not neccessary (however nice), so there may be a simpler way to get to Table_C strictly just from Table_A? I don't know.
Thank you.
... View more