title: MySQL - Query to split an integer bitwise field into a boolean column for each bit
body: <p>Given the following example data:</p>
  
  <pre><code>mysql&gt; select * from test2;
  +-------+--------+
  | name  | status |
  +-------+--------+
  | bob   |      0 |
  | jim   |      2 |
  | karen |      5 |
  | jane  |      1 |
  | roy   |      7 |
  +-------+--------+
  </code></pre>
  
  <p>I want to come up with a query which will split out the value of the status column into a pseudo-boolean value column for each individual bit of the integer.</p>
  
  <p>What I do now is to have the application generate a query that uses a case statement and bitwise and operator for each column to get the result, like so:</p>
  
  <pre><code>mysql&gt; select 
      -&gt;     name, 
      -&gt;     (case when (status &amp; 1) then 'yes' else 'no' end) as bit1, 
      -&gt;     (case when (status &amp; 2) then 'yes' else 'no' end) as bit2, 
      -&gt;     (case when (status &amp; 4) then 'yes' else 'no' end) as bit4, 
      -&gt;     (case when (status &amp; 8) then 'yes' else 'no' end) as bit8 
      -&gt; from test2;
  +-------+------+------+------+------+
  | name  | bit1 | bit2 | bit4 | bit8 |
  +-------+------+------+------+------+
  | bob   | no   | no   | no   | no   |
  | jim   | no   | yes  | no   | no   |
  | karen | yes  | no   | yes  | no   |
  | jane  | yes  | no   | no   | no   |
  | roy   | yes  | yes  | yes  | no   |
  +-------+------+------+------+------+
  5 rows in set (0.00 sec)
  </code></pre>
  
  <p>Now this works, and does so efficiently since there are no sub queries or joins, but it's not very elegant. </p>
  
  <p>I would like a more elegant solution to do this dynamically without having to create a case statement and test for every bit. </p>
  
  <p>This is example data with only a few bits, but in the implementation proper, there will be tens of thousands of rows, and many more bits that can be set.</p>
  
  <p><strong>Bonus:</strong></p>
  
  <p>Base the generation of the columns (column name and test) off of a table containing the definition of each bit like the following:</p>
  
  <pre><code>mysql&gt; select * from bit_spec;
  +-----+------+
  | bit | desc |
  +-----+------+
  |   1 | bit1 |
  |   2 | bit2 |
  |   4 | bit3 |
  |   8 | bit4 |
  +-----+------+
  </code></pre>
  
  <p>Currently, the application handles this, and it's not a hard requirement that this processing be done by the MySQL server instead, but it would be advantageous if it was.</p>
  
  <p>Here's the <a href="http://sqlfiddle.com/#!2/5f6d0/1" rel="nofollow">SQL Fiddle</a> containing the example data, example bit definition table, and my current solution.</p>