如何在MySQL中实现动态Pivot并解决查询结果行重复问题

错误原因 #

你出现重复行的核心问题是GROUP BY子句的字段设置不符合行转列的需求。
你当前GROUP BY了id,question.question_id,question.question_name三个字段，相当于按「用户ID+单个问题」的维度分组，每个用户的每个问题都会单独生成一行，自然会出现同个ID对应多条记录的情况。

正确SQL写法 #

只需要把GROUP BY后面的问题相关字段删除，仅保留id按用户维度分组即可，就能实现每个用户的所有答案聚合到同一行的效果：

SELECT  id,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Household'
             THEN answer.text
             ELSE NULL 
         END
     ) AS Household,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Dependents' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS Dependents,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Generation'
             THEN answer.text
            ELSE NULL 
         END
     ) AS Generation,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Gender' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'Gender',
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Race' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'Race',
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'FinancialGoals' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'FinancialGoals'
FROM user_answers
INNER JOIN answer ON user_answers.answer_id=answer.answer_id
INNER JOIN question ON answer.question_id=question.question_id
GROUP BY id

可选优化 #

• 如果同一个问题用户可能有重复答案，可以在GROUP_CONCAT中加DISTINCT去重：GROUP_CONCAT(DISTINCT answer.text)
• 如果需要自定义多个答案的分隔符，可添加SEPARATOR参数，比如用顿号分隔：GROUP_CONCAT(answer.text SEPARATOR '、')

内容的提问来源于stack exchange，提问作者Mia Mia