title: What SQL query or view will show "dynamic columns"
body: <p>I have a table of data, and I allow people to add meta data to that table.</p>
  
  <p>I give them an interface that allows them to treat it as though they're adding extra columns to the table their data is stored in, but I'm actually storing the data in another table.</p>
  
  <pre><code>Data Table
     DataID
     Data
  
  Meta Table
     DataID
     MetaName
     MetaData
  </code></pre>
  
  <p>So if they wanted a table that stored the data, the date, and a name, then I'd have the data in the data table, and the word "Date" in metaname, and the date in MetaData, and another row in the meta table with "Name" in metaname and the name in metadata. </p>
  
  <p>I now need a query that takes the information from these tables and presents it as though coming from a single table with the two additional columns "Data" and "Name" so to the customer it would look like there's a single table with their custom columns:</p>
  
  <pre><code>MyTable
     Data
     Date
     Name
  </code></pre>
  
  <p>Or, in other words, how do I go from here:</p>
  
  <pre><code>Data Table
     DataID        Data
     1             Testing!
     2             Hello, World!
  
  Meta Table
     DataID        MetaName         MetaData
     1             Date             20081020
     1             Name             adavis
     2             Date             20081019
     2             Name             mdavis
  </code></pre>
  
  <p>To here:</p>
  
  <pre><code>MyTable
     Data          Date             Name
     Testing!      20081020         adavis
     Hello, World! 20081019         mdavis
  </code></pre>
  
  <p>Years ago when I did this in MySQL using PHP, I did two queries, the first to get the extra meta data, the second to join them all together.  I'm hoping that modern databases have alternate methods of dealing with this.</p>
  
  <p>Related to option 3 of <a href="https://stackoverflow.com/questions/218848/design-decision-dynamically-adding-data-question#218872">this question</a>.</p>
  
  <p>-Adam</p>