Mysql从一个表中选择动态行值作为列名，从另一个表中选择值

debugcn 发表于 Dev

利雅得艾哈迈德

我安排了一个调查，其中一个项目有一些问题。用户必须从公众那里收集给定的项目答案。调查表如

用户表

user_id     user_name
  1          User 1   
  2          User 2

项目表

project_id  project_name
  1           project 1    
  2           project 2

问题表

ques_id  project_id  ques_name
  1         1         Question 1
  2         1         Question 2 
  3         1         Question 3
  4         1         Question 4

答案表

ans_id  public_id  user_id ques_id  ques_ans
  1         1      1         1     Answer 1
  2         1      1         2     Answer 2 
  3         1      1         3     Answer 3
  4         1      1         4     Answer 4

现在我想生成一个报告，其中问题表值作为列名与给定的 project_id 匹配，问题答案作为答案表中的值与 ques_id 匹配

而且，她是我的预期输出：

User_Name  public_id  Question 1  Question 2  Question 3  ...
User 1        1         Answer 1   Answer 2    Answer 3   ...

有人建议使用数据透视，但我发现“MySQL 没有对数据透视操作的本机支持”有人可以帮我吗？

利雅得艾哈迈德

最后代码正在运行

SET @sql = NULL;
    SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
    'MAX(IF(a.ques_id = ',
    ques_id,
    ', a.ques_ans, NULL)) AS `',
    ques_name,'`'
    )
    ) INTO @sql
     FROM survey_answer inner join survey_question on  survey_answer.ques_id=survey_question.id;

set @sql = CONCAT('select u.user_name ,q.category_id,a.p_code, ' ,@sql,'  FROM `survey_answer` as a
LEFT JOIN `users` as u ON a.user_id = u.user_id
LEFT JOIN `survey_question` as q ON a.ques_id= q.id');
 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。