title: How do I convert a many-to-many relationship from 2-column table?
body: <p>Suppose I have this database table (some sample code below) that stores the relationship between two lists (requirements and testcases in my case) and I want to create a table with rows showing testcases and columns showing requirements with an indicator showing that a relationship exists.</p>
  
  <p>A few limitations</p>
  
  <ul>
  <li>I don't have the luxury of changing the db structure as this belongs to an open source test case management system (TestLink).</li>
  <li>It's possible to write some code for this, but I'm hoping it can be done in MySQL.</li>
  <li>Ah, and yes, it uses MySQL, so this would have to work in that environment.</li>
  <li><p>This functionality used to exist, but has been taken out because typically, this type of work brings the db to its knees when there are tens-of-thousands of testcases and requirements.</p>
  
  <p>create table <code>pivot</code> ( 
  <code>req_id</code> int(11), 
  <code>testcase_id</code> int(11) 
  ) ;</p>
  
  <p>/*Data for the table <code>pivot</code> */ </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (1,1); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (2,2); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (3,3); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (4,1); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (5,2); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (6,3); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (2,1); </p>
  
  <p>insert into <code>pivot</code>(<code>req_id</code>,<code>testcase_id</code>) values (3,2); </p></li>
  </ul>
  
  <p>What I want to get out of the query is a table that looks somethign like this:</p>
  
  <pre><code>   1    2    3    4    5    6
  1  x    x         x
  2       x    x         x
  3            x              x
  </code></pre>
  
  <p>Note:the row are the testcase_ids and the columns are the 'req_ids'</p>
  
  <p>Anyone have a tip on how to get this with just SQL?</p>