title: How to dynamically add columns and group by NULL's in MySQL Pivot?
body: <p>I have a table with values of name and occupation. I am trying to do 2 things</p>
  
  <ol>
  <li>Pivot the table (achieved)</li>
  <li>Dynamically add / remove columns based on the values</li>
  <li>Reduce the NULL values in columns after pivot</li>
  </ol>
  
  <p>My query:</p>
  
  <pre><code>select Actor, Teacher, Doctor from (
  SELECT
  (case when occupation = (select distinct occupation from occs a order by 1 limit 0,1) then name else NULL end) as "Actor"
  ,(case when occupation = (select distinct occupation from occs a order by 1 limit 1,1) then name else NULL end) as "Teacher"
  ,(case when occupation = (select distinct occupation from occs a order by 1 limit 2,1) then name else NULL end) as "Doctor"
  FROM occs order by 1) x ;
  </code></pre>
  
  <p>Output of above query:</p>
  
  <pre><code>+-------+---------+--------+
  | Actor | Teacher | Doctor |
  +-------+---------+--------+
  | NULL  | NULL    | ketty  |
  | NULL  | Jane    | NULL   |
  | NULL  | Zhan    | NULL   |
  | julia | NULL    | NULL   |
  | meera | NULL    | NULL   |
  +-------+---------+--------+
  </code></pre>
  
  <p>I am trying to get Output as</p>
  
  <pre><code>+-------+---------+--------+
  | Actor | Teacher | Doctor |
  +-------+---------+--------+
  | julia | Jane    | ketty  |
  | meera | Zhan    | NULL   |
  +-------+---------+--------+
  </code></pre>
  
  <p>Additionally in my sql I am specifying the columns Actor, Teacher, Doctor.
  Is there a way that I could scan the table ans get the columns without having to manually do a distinct.</p>
  
  <p>DDL/DML for problem:</p>
  
  <pre><code>CREATE TABLE occs (
    name varchar(10) DEFAULT NULL,
    occupation varchar(10));
  
  
  insert into occs values ('julia','Actor');
  insert into occs values ('meera','Actor');
  insert into occs values ('ketty','Teacher');
  insert into occs values ('Jane','Doctor');
  insert into occs values ('Zhan','Doctor');
  </code></pre>