title: Mysql query to pull data in the form of matrix
body: <p>I have data in database with three columns id, topic, and subtopic. Something like this,</p>
  
  <pre><code>CREATE TABLE Table2 (`id` int, `topic` varchar(5), `subtopic` varchar(6));
  INSERT INTO Table2 (`id`, `topic`, `subtopic`) VALUES
      (1, 'place', 'paris'),
      (1, 'group', 'A'),
      (1, 'group', 'B'),
      (2, 'place', 'us'),
      (2, 'group', 'C'),
      (3, 'group', 'A'),
      (3, 'water', 'salt'),
      (4, 'water', 'sweet'),
      (4, 'world', 'ep'),
      (5, 'place', 'venus'),
      (5, 'place', 'paris'),
      (5, 'group', 'A');
  </code></pre>
  
  <p>I want to output the result matrix place vs group from topic. Something like this.</p>
  
  <pre><code>    Paris|US|Venus
  A   2    |0 | 1
  B   1    |0 | 0
  C   0    |1 | 0
  </code></pre>
  
  <p>Idea is to pick up all the values of 'Group' (A,B,C) and 'Places' (paris,us,venus) in subtopic column. And then find number of co-occurrences with such condition. </p>
  
  <p>Any idea how to solve in MySql?</p>