title: MySQL pivot row into dynamic number of columns
body: <p>Lets say I have three different MySQL tables:</p>
  
  <p><strong>Table <code>products</code>:</strong> </p>
  
  <pre><code>id | name
   1   Product A
   2   Product B
  </code></pre>
  
  <p><strong>Table <code>partners</code>:</strong> </p>
  
  <pre><code>id | name
   1   Partner A
   2   Partner B
  </code></pre>
  
  <p><strong>Table <code>sales</code>:</strong></p>
  
  <pre><code>partners_id | products_id
            1             2
            2             5
            1             5
            1             3
            1             4
            1             5
            2             2
            2             4
            2             3
            1             1
  </code></pre>
  
  <p>I would like to get a table with partners in the rows and products as columns. So far I was able to get an output like this:</p>
  
  <pre><code>name      | name      | COUNT( * )
  Partner A   Product A          1
  Partner A   Product B          1
  Partner A   Product C          1
  Partner A   Product D          1
  Partner A   Product E          2
  Partner B   Product B          1
  Partner B   Product C          1
  Partner B   Product D          1
  Partner B   Product E          1
  </code></pre>
  
  <p>Using this query:</p>
  
  <pre><code>SELECT partners.name, products.name, COUNT( * ) 
  FROM sales
  JOIN products ON sales.products_id = products.id
  JOIN partners ON sales.partners_id = partners.id
  GROUP BY sales.partners_id, sales.products_id
  LIMIT 0 , 30
  </code></pre>
  
  <p>but I would like to have instead something like:</p>
  
  <pre><code>partner_name | Product A | Product B | Product C | Product D | Product E
  Partner A              1           1           1           1           2
  Partner B              0           1           1           1           1
  </code></pre>
  
  <p>The problem is that I cannot tell how many products I will have so the column number needs to change dynamically depending on the rows in the products table. </p>
  
  <p>This very good answer does not seem to work with mysql: <a href="https://stackoverflow.com/questions/2922797/t-sql-pivot-possibility-of-creating-table-columns-from-row-values">T-SQL Pivot? Possibility of creating table columns from row values</a></p>