How to SELECT a column composed by some other columns value

Question

I have a daycode column that stores values like 1,2...7
then in a different table I have cols like field1,field2...field7

I can join them on a key, but how do I select the specific fieldX column based on values passed?

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

Where each fieldX represents a value for monday, tuesday and so on till sunday.

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

Update

t2 has columns like field1, field2 ... field7, and daycode values is 1,2 ... 7. We need to concat "field" with the value taken from daycode column.

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

Answer 1

You can create the statement in string an then execute it using execute (@sql) or You can add a case statement in the select where You will pick the proper column.

Im not sure aobut this but you can try

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;