Databases
StackOverflow2013
Postsdbo
POWhat SQL query or view will show "dynamic columns"
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat SQL query or view will show "dynamic columns"
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload