Map Multiple Fields from Single Field by Text

[sjhussain]

Hello,

Hope all is well with everyone.

How can we map single field to multiple fields.

We have two tables. Datalog and AttachmentUnit - they are linked by tun_id_pk

In DataLog we have fields tun_id_pk, log_di1, log_di2, log_di3, log_d4.  DataLog is a huge table.  One month Data is about 30 million in DataLog Table.

In AttachmentUnit table there are fields atu_id, tun_id_pk (link with DataLog) and  atu_port ("di0", "di1", "di2", "di3".  Each atu_id will have one port number.

AttachmentUnit:

atu_id, tun_id_pk, atu_port

1, 1, "di0"

2, 1, "di1"

3, 2, "di2"

4, 3, "di3"

If inserter_port is di1 then it means that value for the Datalog tun_id_pk for the corresponding Attachment Unit will be in log_di1.  Similarly if the inserted_port is di2 then corresponding value will be in log_di2.

How can we combine the Datalog and Attachment Unit table into one ?

DataLog:

tun_id_pk, log_di0, log_di1, log_di2, log_di3

1, 12,0,0,0

1, 0,34,0,0

2, 0,0,13,0

3, 0,0,0,123

Result:

New_DataLog

tun_id_pk, atu_id, value

1, 1, 12

1, 2, 34

2, 2, 13

4, 3, 123

In the example above

TUN_ID is 1 and ATU_ID is 1 and the INSERTED_PORT is "di0" so in the DataLog table it maps to log_di0 where the value is 12.

So we have to map the inserted_port with log_di numbers and then insert it in the resultant table with the value.

Will appreciate if someone can assist.

Thanks.

[vinieme12]

please provide sample data, it'll be easier to show and explain

[sjhussain]

‌Dear Vineeth

Thanks for the quick response.  Actually i have included a small sample of data in my query.  There also exists the result table which I wouls like to follow.

kindly let me lnow if you require more info.

THanks

[vinieme12]

See Attached

Cheers

V