How to select a column consisting of several column values

Question

I have a column daycodethat stores values ​​such as 1,2 ... 7
then in another table I have cols likefield1,field2...field7

I can join them on key, but how to select a specific column fieldXbased on the values ​​passed?

Table 1 has the following columns
-------------
id
prodno
field1
field2
field3
field4
field5
field6
field7

Where each field X represents a value for Monday, Tuesday, etc. until Sunday.

Table 2 has the following columns
-------------
id
prodno
dt
daycode

Update

t2 has columns such as field1, field2 ... field7, and the values ​​of the daily code are 1.2 ... 7. We need to execute the "field" with the value taken from the column of the daily code.

select table1.id,select [concat('field',table2.daycode)] from table1 join
table2 on table1.id=table2.key

Answer 1

, execute (@sql) case select, .

, ,

SELECT t1.id, 
 CASE 
   WHEN daycode = 1 THEN t2.field1 
   WHEN daycode = 2 THEN t2.field2 
   WHEN daycode = 3 THEN t2.field3 
   WHEN daycode = 4 THEN t2.field4 
 END
FROM t1 join t2 on t1.id=t2.key;