MySQL数据透视表的列数据作为行

大佬教程收集整理的这篇文章主要介绍了MySQL数据透视表的列数据作为行，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

SELECT  a.ID,
        a.user_iD,
        a.@R_958_6186@,
        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.@R_958_6186@

• SQLFiddle演示

如果您不清楚问题的数量（ 例如Matei Mihai所说的1000个 ），则非常需要动态版本。

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.@R_958_6186@, ', @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.@R_958_6186@');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• SQLFiddle演示

输出值

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

Question table
+----+-------------+
| id | question    |
+----+-------------+
| 1  | Is it this? |
| 2  | Or this?    |
| 3  | Or that?    |
+----+-------------+

Results Table
+----+---------+--------+
| id | user_id | @R_958_6186@ |
+----+---------+--------+
| 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            |
+----+-------------------------+--------------+

+-----------+---------+--------+-------------+----------+----------+
| result_id | user_id | @R_958_6186@ | 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       |
+-----------+---------+--------+-------------+----------+----------+

大佬总结

以上是大佬教程为你收集整理的MySQL数据透视表的列数据作为行全部内容，希望文章能够帮你解决MySQL数据透视表的列数据作为行所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。