I want to be able to look up fields values from a secondary table that stores the secondary table field names as strings in a primary table.

I am trying to both query the primary table itself to get the field names I want from the secondary table:

How can I use that string that contains the field name to select data from table2, given the id of a specific row.

The use case here, is that table1 also contains a note field. I want to populate that note field with the contents of a specific record in table2. I want to do this for all the records in table 1: each record references a different field by field name.

Another way to look at this: Table2 has 1 row we want data from, and we know the ID table1 stores the list of fields that we need data for from table 2 How do I get both pieces of data (field name, and field value) using these constraints?

all data is varchar

table1
fieldname externalID
myField1 001
myField2 001

table2
id myField1 myField2 myField3
001 myField1ValueForID001 myField2ValueForID001 myField3ValueForID001
002 moredata1 moredata2 moredata3

select fieldname,
(select [fieldname] from table2 where id = ) as fieldData
from table1

result
fieldname fieldData
myField1 myField1
myField2 myField2

desired result
fieldname fieldData
myField1 myField1ValueForID001
myField2 myField2ValueForID001