title: Complex SQL Pivot
body: <p>So I've got a query selecting data from 4 tables: </p>
  
  <ul>
  <li>cases</li>
  <li>taxonomies (containing titles and data about taxonomies)</li>
  <li>taxonomy_values (possible values for the taxonomies)</li>
  <li>post_taxonomy_values (holds IDs from the case table, and IDs from the taxonomy table to link the two)</li>
  </ul>
  
  <p>I'm attempting to select a row from the cases table and then get all taxonomy names and their values. Here's what I've got so far:</p>
  
  <pre><code>select 
  cases.title, cases.content, cases.status, taxonomies.title as 'taxonomy', taxonomy_values.value as 'taxonomy_value'
  
  from cases, post_taxonomy_values, taxonomies, taxonomy_values 
  
  where 
  cases.slug = 'b-v-dpp' and
  post_taxonomy_values.post = cases.id and
  taxonomies.id = post_taxonomy_values.taxonomy and
  taxonomy_values.id = post_taxonomy_values.value
  </code></pre>
  
  <p>But this is what that generates:
  <img src="https://i.stack.imgur.com/H9HoA.png" alt="Current query result"></p>
  
  <p>So basically what I'd like is to take the taxonomy names and make them column names, and set their values as part of the returned row.</p>
  
  <p>Here's the structure of the tables:</p>
  
  <p><strong>cases:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/uaTf2.png" alt="cases table"></p>
  
  <p><strong>taxonomies:</strong></p>
  
  <p><img src="https://i.stack.imgur.com/v68lx.png" alt="taxonomies table"></p>
  
  <p><strong>taxonomy_values</strong> ('taxonomy' references an ID in the taxonomies table):</p>
  
  <p><img src="https://i.stack.imgur.com/D9ffh.png" alt="taxonomy_values table"></p>
  
  <p><strong>post_taxonomy_values</strong> ('taxonomy' references an ID in the taxonomies table and 'value' references an ID in the taxonomy_values table):</p>
  
  <p><img src="https://i.stack.imgur.com/satJh.png" alt="post_taxonomy_values table"></p>
  
  <p>I've written and re-written this query but I just can't get my head round the problem, any help would be much appreciated!</p>