0

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.

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

2 Answers 2

0

This is similar to pivot table , Try This

create view pivot_questions as (
  select
    questions.*,
    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;
1
  • It returns 5 rows per participant, I just want to return one row per participant with 5 column as question answer. Feb 5, 2018 at 10:23
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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