Mysql select dynamic row values as column name from a table and value from another table

Riyadh Ahmed

I have arranged a survey where a project has some questions. Users have to collect the given project answer from public. Survey tables like

user tables

user_id     user_name
  1          User 1   
  2          User 2

Project table

project_id  project_name
  1           project 1    
  2           project 2

Question table

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

Answer table

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

Now i want to generate a reports where question table values as column name matched by given project_id and question answers as value from answer table matched by ques_id

And, her is my expected output:

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

Someone suggested to use pivot but i found "MySQL doesn't have native support for pivoting operations" can anyone help me out?

Riyadh Ahmed

Finally the code is working

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;

Collected from the Internet

Please contact [email protected] to delete if infringement.