1
SELECT person.firstnames,
       person.surname,
       crm_question.label,
       crm_answer.value
       FROM crm_interaction
    JOIN person ON person.id = crm_interaction.person_id
    JOIN crm_answer ON crm_answer.interaction_id = crm_interaction.id
    JOIN crm_question ON crm_question.id = crm_answer.question_id

my output looks like this

firstnames | surname | label | value  | 
|----------|------   |-------|-----   |
|     name | surname |label 1| value 1| 
|     name | surname |label 2| value 2| 
|     name | surname |label 3| value 3| 

But I need it to look like this

firstnames | surname | label 1 | label 2 | label 3 |
|----------|------   |-------   |-----    |
|     name | surname |value 1   | value 2 | value 3 | 
|     name | surname |value 1   | value 2 | value 3 |
|     name | surname |value 1   | value 2 | value 3 | 

I created a quick SQL fiddle to build my Schema here:

http://sqlfiddle.com/#!9/18e20/1

I have tried to impliment a procedure into my code but keep getting errors that the syntax is incorrect I have never used MYSQLand this is greek to me.

Is it possible to pivot the original statement using a procedure to output the data in this way?

I know that tables can join and inner join but how can one go about changing the way that the data is displayed and connected?

  • Is the number of labels static? – Philᵀᴹ Jan 7 '16 at 10:13
  • No they need to be dynamic – Devin Gray Jan 7 '16 at 10:14