title: Dynamically Selecting value from column table based on column name from other table (reflection)
body: <p>Assume I have 2 tables FOO and BAR as below, is it possible to use a sort of reflection on FOO, if you know the column name as a string based on a join of the BAR table?</p>
  
  <pre><code>SELECT DB, FOO.Name, FOO.Type, BAR.Field, I.DATA_TYPE AS FType, FOO.**&lt;BAR.FIELD&gt;** AS Value
  FROM INFORMATION_SCHEMA.COLUMNS AS I  
  inner JOIN BAR ON I.COLUMN_NAME = BAR.Field
  inner JOIN FOO ON FOO.TYPE = BAR.TYPE
  WHERE DB = 4 AND FLAG = 1
  </code></pre>
  
  <p>i.e. for each selected row, FOO.<strong></strong> need to change to reflect the value of the matching column in FOO, i.e. if one row has BAR {4, AC1, LO} and FOO { 4, AC1, LO, COL1} I want the value of 1 to be picked.</p>
  
  <p>I know that I can probably do this is 2 rounds and merge the the data, however I wondered if anybody would know of a way to do this more efficiently in 1 go, saving code path.</p>
  
  <p>I should add, I generally have around 60 columns in either table, and they are pretty random, i.e. I cannot assume that either col1, 2 or 3 exist, I can only go by what is in the equivalent BAR table.</p>
  
  <p>FOO:</p>
  
  <pre><code>+--------+--------+---------+---------+--------+-------+
  |   DB   | Name   | Type    | Col1    |  Col2  | Col3  |
  +--------+--------+---------+---------+--------+-------+
  |   4    | AC1    | LO      | 1       |  10    | 2     |
  |   4    | AC1    | HI      | 2       |  20    | 4     |
  |   1    | DC2    | HI-HI   | 11      |  5     | 2     |
  |   1    | DC2    | HI      | 22      |  10    | 4     |
  |   1    | DC2    | LO      | 33      |  15    | 6     |
  +--------+--------+---------+---------+--------+-------+
  </code></pre>
  
  <p>BAR:</p>
  
  <pre><code>+--------+--------+---------+---------+--------+
  |   DB   | Name   | Type    | Field   |  Flag  |
  +--------+--------+---------+---------+--------+
  |   4    | AC1    | LO      | Col1    |  1     |
  |   4    | AC1    | HI      | Col1    |  1     |
  |   1    | DC2    | HI-HI   | Col1    |  1     |
  |   1    | DC2    | HI      | Col1    |  1     |
  |   1    | DC2    | LO      | Col1    |  1     |
  |   4    | AC1    | LO      | Col2    |  0     |
  |   4    | AC1    | HI      | Col2    |  0     |
  |   1    | DC2    | LO      | Col2    |  0     |
  |   1    | DC2    | HI-HI   | Col2    |  0     |
  |   1    | DC2    | HI      | Col2    |  0     |
  |   4    | AC1    | LO      | Col3    |  0     |
  |   4    | AC1    | HI      | Col3    |  0     |
  |   1    | DC2    | LO      | Col3    |  0     |
  |   1    | DC2    | HI-HI   | Col3    |  0     |
  |   1    | DC2    | HI      | Col3    |  0     |
  +--------+--------+---------+---------+--------+
  </code></pre>
  
  <p>RESULT:</p>
  
  <pre><code>+--------+--------+---------+---------+--------+--------+
  |   DB   | Name   | Type    | Field   |  FTYPE |  VALUE |
  +--------+--------+---------+---------+--------+--------+
  |   4    | AC1    | LO      | Col1    |  float |  1     |
  |   4    | AC1    | HI      | Col1    |  float |  2     |
  |   4    | AC1    | LO      | Col2    |  float |  10    |
  |   4    | AC1    | HI      | Col2    |  float |  20    |
  |   4    | AC1    | LO      | Col3    |  float |  2     |
  |   4    | AC1    | HI      | Col3    |  float |  4     |
  +--------+--------+---------+---------+--------+--------+
  </code></pre>