title: Mysql dynamic table generation from normalized data
body: <p>My goal is to store a form. </p>
  
  <p><strong>Form structure:</strong> </p>
  
  <p>Section1</p>
  
  <p>section1 table</p>
  
  <p>Section2 </p>
  
  <p>section2 table</p>
  
  <p>....</p>
  
  <p>Section S</p>
  
  <p>sectionS table</p>
  
  <p>The tables look like these except i need sum (totals) for both rows and columns. Tables are dynamic in size M by N</p>
  
  <p><a href="https://i.stack.imgur.com/14ja7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/14ja7.png" alt="enter image description here"></a></p>
  
  <p>I need to generate 2D table values (with slight variations) on many pages so I want it to be done by the mysql sever instead of with PHP. I’m not sure if I should use views, prepared queries/pivot table, or PHP for this.  </p>
  
  <p>I believe this is the correct database schema for storing the form (corrections / suggestions wanted). </p>
  
  <p><a href="https://i.stack.imgur.com/daw4B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/daw4B.png" alt="Schema"></a></p>
  
  <p>I like this idea <a href="https://stackoverflow.com/a/15832331">stackoverflow post</a>, but It's too far over my head. This is what i have finished before stopping to make sure there is not a better solution. </p>
  
  <pre><code>set @sql = 'COSID ';    
  set @sql = concat('select CommitSources.StandardSource, ', @sql, "from CommitSources where CommitSources.CSID = '20' group by CommitSources.COSID;");
  PREPARE stmt FROM @sql;    
  EXECUTE stmt;
  </code></pre>
  
  <p><strong>Question:</strong></p>
  
  <p>I would like a solution that allows me to send a form PK and section PK and return rows like this so that I can easily manipulate/output them. (*Note this output is missing a row with column totals)</p>
  
  <p><a href="https://i.stack.imgur.com/1kwFT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1kwFT.png" alt="Output format"></a></p>
  
  <ol>
  <li>What is the best way to do this? (PHP, mysql view, mysql prepared, other)</li>
  <li>Is my database schema correct and normalized correctly for storing this data?</li>
  </ol>