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

Question

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?

Possible duplicate of: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns — ad_on_is, May 14 '17 at 09:28
On the assumption that each user can answer each question only once, answer_id serves no purpose — Strawberry, May 14 '17 at 09:31
That aside, ignore the suggested duplicate and instead consider handling issues of data display in application code — Strawberry, May 14 '17 at 09:33

Alexander · Answer 1 · 2017-05-15T10:14:18.567

1

You can use another output format of the query. For example:

SELECT user_name, answer.project_id, ques_name, ques_ans
FROM 
    `answer` 
    INNER JOIN `user` USING (user_id)
    INNER JOIN `question` USING (ques_id);

To restrict rows by specific project add WHERE clause:

 WHERE project_id = @ProjectID

Then transform the result to the desired view using PHP.
If it is critical to solve the question using MySQL then create new colums manually using aliaces. To aggregate rows by user and project use GROUP BY clause. To show the possible non-empty values use MAX() function. In your case:

SELECT
  user_name, project_id,
  MAX(IF(ques_name = 'Question 1', ques_ans, NULL)) AS `Question 1`,
  MAX(IF(ques_name = 'Question 2', ques_ans, NULL)) AS `Question 2`,
  MAX(IF(ques_name = 'Question 3', ques_ans, NULL)) AS `Question 3`,
  MAX(IF(ques_name = 'Question 4', ques_ans, NULL)) AS `Question 4`
FROM
    (SELECT 
        ans_id, user_id, user_name, answer.project_id, ques_name, ques_ans
    FROM 
        answer
        INNER JOIN `user` USING (user_id)
        INNER JOIN question USING (ques_id)
        ) AS tmp
GROUP BY
  user_id, project_id;

edited May 15 '17 at 10:14

answered May 14 '17 at 09:28

Alexander

4,420
7
27
42

@RiyadhAhmed, my answer is a "plan B". – Alexander May 14 '17 at 15:14
plan B? actually i am still trying to transform the result by following the [link](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) but cannot get working... – Riyadh Ahmed May 15 '17 at 05:37
@Riyadh Ahmed, you are right. If it is critical to solve the question using MySQL then you could try to adapt [existing solution](http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns) for your case. Otherwise use "plan B". – Alexander May 15 '17 at 05:52
@ Alexander i have tried that existing solution but failed.Can you please help me how can i transform the result or use existing solution to solve? – Riyadh Ahmed May 15 '17 at 08:00
@Riyadh Ahmed, you are persistent :) See new version of my answer. – Alexander May 15 '17 at 10:16
sorry for late. i solved the query but facing a problem to run the query using codeigniter. how can i execute the prepare stmt? – Riyadh Ahmed May 15 '17 at 11:52

score 1 · Accepted Answer · answered May 15 '17 at 11:48

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;

score 0 · Answer 3 · edited May 23 '17 at 12:03

0

your query should be :

SELECT an.user_name,an.public_id,qs.quest_name,an.quest_answer 
FROM answer_table an,question_table qs,user_table usr
WHERE an.quest_id = qs.quest_id
AND an.user_id=usr.user_id

For the table rotation : here a similar question about rotating tables

mysql pivot table date (vertical to horizontal data)

edited May 23 '17 at 12:03

Community

1
1

answered May 14 '17 at 09:31

lagripe

766
6
18

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

3 Answers3