title: mysql a column into multiple columns based on the value
body: <pre><code>mysql&gt; select description from devices where id=172;
  +--------------------------------------------------------------------------------+
  | description                                                                    |
  +--------------------------------------------------------------------------------+
  | Fault: 'HYD OIL TEMP HIGH' from 'Controller' of type 'SYSTEM' with code '1003' |
  +--------------------------------------------------------------------------------+
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>The above is the pattern in which I have records in my database. I am trying to split them into multiple columns like this just for running a report.</p>
  
  <pre><code>+---------+------------------------+--------------+---------+---------+
  | status  |  description           | device       | system  |   code  |
  +---------+------------------------+--------------+---------+---------+
  | Fault   | HYD HYD OIL TEMP HIGH  | controller   | SYSTEM  | 1003    |
  +---------+------------------------+--------------+---------+---------+
  </code></pre>
  
  <p>I know the above stuff which I am doing is worse. I don't want to change the ETL for running a one time report. This is what I have tried.</p>
  
  <pre><code>mysql&gt; select substr(description, 1, locate(":", description)-1) as status from devices where id=172;
  +--------+
  | status |
  +--------+
  | Fault  |
  +--------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>The database is over amazon RDS. So I cannot use <a href="http://www.mysqludf.org/lib_mysqludf_preg/" rel="nofollow">lib_mysqludf_preg</a>. 
  I can only do plain SQL. Help is appreciated. Thanks.</p>