Dati della colonna tabella pivot MySQL come righe

Sto lottando per trovare una soluzione a questo problema MySQL. Non riesco proprio a capire come farlo. Ho le seguenti tabelle.

Question table +----+-------------+ | id | question | +----+-------------+ | 1 | Is it this? | | 2 | Or this? | | 3 | Or that? | +----+-------------+ Results Table +----+---------+--------+ | id | user_id | job_id | +----+---------+--------+ | 1 | 1 | 1 | | 2 | 1 | 3 | | 3 | 2 | 3 | +----+---------+--------+ Answers table +----+-------------------------+--------------+ | id | answer | fk_question_id | fk_result_id | +----+-------------------------+--------------+ | 1 | Yes | 1 | 1 | | 2 | No | 2 | 1 | | 3 | Maybe | 3 | 1 | | 4 | Maybe | 1 | 2 | | 5 | No | 2 | 2 | | 6 | Maybe | 3 | 2 | | 7 | Yes | 1 | 3 | | 8 | Yes | 2 | 3 | | 9 | No | 3 | 3 | +----+-------------------------+--------------+ 

Se ansible mi piacerebbe visualizzare le risposte alle domande come colonne per ogni set di risultati, come questo.

 +-----------+---------+--------+-------------+----------+----------+ | result_id | user_id | job_id | Is it this? | Or this? | Or that? | +-----------+---------+--------+-------------+----------+----------+ | 1 | 1 | 1 | Yes | No | Maybe | | 2 | 1 | 3 | Maybe | No | Maybe | | 3 | 2 | 3 | Yes | Yes | No | +-----------+---------+--------+-------------+----------+----------+ 

Qualsiasi aiuto sarebbe molto apprezzato.

Grazie

 SELECT a.ID, a.user_ID, a.job_id, MAX(CASE WHEN c.question = 'Is it this?' THEN b.answer END) 'Is it this?', MAX(CASE WHEN c.question = 'Or this?' THEN b.answer END) 'Or this?', MAX(CASE WHEN c.question = 'Or that? ' THEN b.answer END) 'Or that? ' FROM Results a INNER JOIN Answers b ON a.id = b.fk_result_id INNER JOIN Question c ON b.fk_question_id = c.ID GROUP BY a.ID, a.user_ID, a.job_id 
  • Demo di SQLFiddle

Se hai un numero inconsapevole di domande (in particolare 1000 come ha detto Matei Mihai ), è necessaria una versione dynamic.

 SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(CASE WHEN c.question = ''', question, ''' then b.answer end) AS ', CONCAT('`',question,'`') ) ) INTO @sql FROM Question; SET @sql = CONCAT('SELECT a.ID, a.user_ID, a.job_id, ', @sql, ' FROM Results a INNER JOIN Answers b ON a.id = b.fk_result_id INNER JOIN Question c ON b.fk_question_id = c.ID GROUP BY a.ID, a.user_ID, a.job_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; 
  • Demo di SQLFiddle

PRODUZIONE

 ╔════╦═════════╦════════╦═════════════╦══════════╦══════════╗ ║ ID ║ USER_ID ║ JOB_ID ║ IS IT THIS? ║ OR THIS? ║ OR THAT? ║ ╠════╬═════════╬════════╬═════════════╬══════════╬══════════╣ ║ 1 ║ 1 ║ 1 ║ Yes ║ No ║ Maybe ║ ║ 2 ║ 1 ║ 3 ║ Maybe ║ No ║ Maybe ║ ║ 3 ║ 2 ║ 3 ║ Yes ║ Yes ║ No ║ ╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝