MySQL Pivot表列数据作为行

I'm struggling to find a solution this MySQL problem. I just can't seem to get my head around how to do it. I have the following tables.

我在努力寻找解决MySQL问题的方法。我就是不知道怎么做。我有以下几张桌子。

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            |
+----+-------------------------+--------------+

If possible I'd like to display the question answers as columns for each result set, like this.

如果可能的话，我想为每个结果集显示问题答案作为列，如下所示。

+-----------+---------+--------+-------------+----------+----------+
| 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       |
+-----------+---------+--------+-------------+----------+----------+

Any help would be much appreciated.

非常感谢您的帮助。

Thanks

谢谢

1 个解决方案

#1

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

• SQLFiddle Demo
• SQLFiddle演示

If you have unknow number of questions (specifically 1000 like Matei Mihai said), a dynamic version is much required.

如果您有不知道的问题数量(特别是1000个问题，如Matei Mihai所说)，则需要动态版本。

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;

• SQLFiddle Demo
• SQLFiddle演示

OUTPUT

输出

╔════╦═════════╦════════╦═════════════╦══════════╦══════════╗
║ 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       ║
╚════╩═════════╩════════╩═════════════╩══════════╩══════════╝