title: SQL statement - dynamically merging columns from different tables into a single result set
body: <p>I am not sure this is possible with SQL (say we are executing on DB2) - infact I would say it is not, but thought I would ask some folk who are more experienced in SQL than I am to comment.  I have described the problem and what I would like to happen below.  Please let me know if you see a way to do this, if not I guess I am down the path of retrieving the data in batches or something like that.  Thanks a lot.</p>
  
  <pre>
  MAIN TABLE
  
  | REF_TABLE   | RECORD_NO      | GROUPID  |
  |   TABLE1    |   1            |  BLUE    |
  |   TABLE1    |   2            |  BLUE    |
  |   TABLE2    |   3            |  GREEN   |
  |   TABLE3    |   4            |  BLUE    |
  |   TABLE2    |   5            |  GREEN   |
  |   TABLE4    |   6            |  BLUE    |
  
  TABLE1
  
  |  RECORD_NO |   NAME    | VALUE     |
  |   1        |   NAMEX   |  RANDOM1  |
  |   2        |   NAMEY   |  RANDOM2  |
  
  TABLE2
  
  |  RECORD_NO |   NAME    |  VALUE     |
  |   3        |   NAMEB   |  RANDOM10  |
  |   5        |   NAMEC   |  RANDOM9   |
  
  TABLE3
  
  | RECORD_NO  | NAME      | VALUE      |
  |    4       |   NAMET   |  RANDOM77  |
  
  TABLE4
  
  | RECORD_NO  | NAME      | VALUE      |
  |   6        |   NAMET   |  RANDOM77  |
  |   7        |   NAMEZ   |  RANDOM99  |
  
  </pre>
  
  <p>So I have some criteria for querying MAIN TABLE e.g.</p>
  
  <pre><code>SELECT REF_TABLE, RECORD_NUMBER WHERE GROUPID = 'BLUE'
  </code></pre>
  
  <p>But I also want to include the appropiate values from the other tables which are referenced via REF_TABLE.  So the result would be:</p>
  
  <pre>
  | REF_TABLE   | RECORD_NUMBER  | NAME    | VALUE     |
  |   TABLE1    |   1            | NAMEX   |  RANDOM1  |
  |   TABLE1    |   2            | NAMEY   |  RANDOM2  |
  |   TABLE3    |   4            | NAMET   |  RANDOM77 |
  |   TABLE4    |   6            | NAMET   |  RANDOM77 |
  </pre>
  
  <p>In this case TABLE1, TABLE3 and TABLE4 have their name and value columns merged into a single result set.  The table is of course retrieved from the ref_table column of main table.  The list of ref tables in finite, so I could hardcode the table names in SQL statements in the query (to avoid dynamically setting the table name) e.g. <code>IF TABLE1 SELECT FROM SCHEMA.TABLE1 (maybe CASE).</code></p>
  
  <p>Restructuring the tables is not an option and the number of returned results may be 10000s.</p>
  
  <p>Preferable I would want this all as a single query.</p>
  
  <p>Thanks.</p>