selecting specific fields during runtime from table-CodePudding

How do we select fields dynamically?

I've got a table Table1:

 ---------- ------- ---- 
| Table1Id | x     | y  |
 ========== ======= ==== 
| 52       | alex  | aa |
 ---------- ------- ---- 
| 43       | liza  | aa |
 ---------- ------- ---- 
| 21       | harry | bb |
 ---------- ------- ---- 
| 21       | harry | bb |
 ---------- ------- ----

I'd like to join on this Table2:

 ---------- ---------- -------- ------ 
| Table2Id | Table1Id | aa     | bb   |
 ========== ========== ======== ====== 
| 1        | 52       | red    | tall |
 ---------- ---------- -------- ------ 
| 2        | 43       | blue   | thin |
 ---------- ---------- -------- ------ 
| 3        | 21       | orange | fat  |
 ---------- ---------- -------- ------

The result I'm looking for is:

 ------- ------- ---- ---------- 
| xyzid | x     | y  | NewField |
 ======= ======= ==== ========== 
| 52    | alex  | aa | red      |
 ------- ------- ---- ---------- 
| 43    | liza  | aa | blue     |
 ------- ------- ---- ---------- 
| 21    | harry | bb | fat      |
 ------- ------- ---- ----------

As you can see, Table1 has data in the y column the exact field name to grab from Table2.

How do select specific fields from a table, where those fields are actually stored as data in another table?

CodePudding user response：

You can select columns dynamically using CASE, as in:

select
  a.Table1Id as xyzid,
  a.x,
  a.y,
  case
   when a.y = 'aa' then b.aa
   when a.y = 'bb' then b.bb
  end as NewField
from (select distinct * from table1) a
join table2 b on b.Table1Id = a.Table1Id

Result:

 xyzid  x      y   newfield 
 ------ ------ --- -------- 
 52     alex   aa  red      
 43     liza   aa  blue     
 21     harry  bb  fat

See running example at