I have a table with 3 columns, participant_id, question, answer. for each participant, there will be five question.

How do fetch a report like against each participant each question as a column header and answer as value. So in report for each participant there will be one entry in report?

table structure:

expected output: group using participant_id and each question should be the column header and custtusponding answer will be the column value.

  • 1
    Please add tables schema, sample data and the desired result. – McNets Feb 5 '18 at 8:17
  • @McNets Thanks for quick response ,added schema details to the question – Shamseer Pc Feb 5 '18 at 9:17
  • 1
    Still not clear, is there any question_id, please add you CREATE TABLE statement. – McNets Feb 5 '18 at 9:19
  • @McNets yes question field is the question_id – Shamseer Pc Feb 5 '18 at 9:22
  • "and all the children are above average". – Rick James Feb 11 '18 at 16:02

This is similar to pivot table , Try This

create view pivot_questions as (
    case when question = "question1" then itemvalue end as question1,
    case when itemname = "question2" then itemvalue end as question2,
    case when itemname = "question3" then itemvalue end as question3,
    case when itemname = "question4" then itemvalue end as question4,
    case when itemname = "question5" then itemvalue end as question5
  from questions

select * from pivot_questions;
| improve this answer | |
  • It returns 5 rows per participant, I just want to return one row per participant with 5 column as question answer. – Shamseer Pc Feb 5 '18 at 10:23

you can add another condition in select query

select participant_id, 
GROUP_CONCAT(question1 SEPARATOR '') as question1,
GROUP_CONCAT(question2 SEPARATOR '') as question2,
GROUP_CONCAT(question3 SEPARATOR '') as question3,
GROUP_CONCAT(question4 SEPARATOR '') as question4,
GROUP_CONCAT(question5 SEPARATOR '') as question5 
  from pivot_questions
  group by participant_id
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.