title: switching MySQL columns into rows
body: <p>I have a column in mysql and would like to change the column into [0 1 0 1] type for data processing,</p>
  
  <p>for example,</p>
  
  <pre><code>+------+
  | area |
  +------+
  | 1    |
  | 2    |
  +------+
  </code></pre>
  
  <p>I would like to change the form above into the following form:</p>
  
  <pre><code>+--------+--------+
  | apple  | banana |
  +--------+--------+
  |   1    |   0    |
  |   0    |   1    |
  +--------+--------+
  </code></pre>
  
  <p>in short 
  if area = '1', then apple = 1
  if area = '2', then banana = 1</p>
  
  <p>My code is as follows after referencing to some websites but it doesn't work..</p>
  
  <pre><code>insert into test1 (apple, banana)
      -&gt; select count(case when area = '1' then 1 else 0 end) as apple, 
      -&gt; select count(case when area = '2' then 1 else 0 end) as banana
      -&gt; from test3; 
  </code></pre>
  
  <p>the error says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select count(case when area = '2' then 1 else 0 end) as banana 
  from test3' at line 3",
  but I cannot find any reasons.. 
  could someone please kindly help me..?</p>
  
  <p>Thanks ..>_&lt;</p>