Implement Dynamic pivot in MYSQL

Question

I write an mysql query

SELECT id,GROUP_CONCAT(answer.text) as answer,question.question_name as question
FROM user_answers
inner join answer on user_answers.answer_id=answer.answer_id
inner join question on answer.question_id=question.question_id
GROUP BY id,question.question_id

I am getting a result as

But I want the answer as

How can I pivot my data. ?

I tried this query . But not getting actual result. its duplicating

SELECT  id,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Household'
             THEN answer.text
             ELSE NULL 
         END
     ) AS Household,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Dependents' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS Dependents,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Generation'
             THEN answer.text
            ELSE NULL 
         END
     ) AS Generation,
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Gender' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'Gender',
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'Race' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'Race',
     GROUP_CONCAT(
         CASE 
             WHEN question.question_name = 'FinancialGoals' 
             THEN answer.text
             ELSE NULL 
         END
     ) AS 'FinancialGoals'
FROM user_answers
inner join answer on user_answers.answer_id=answer.answer_id
inner join question on answer.question_id=question.question_id
GROUP BY id,question.question_id,question.question_name

result is

Added aggressive function and still result column is duplicating

hi @Luuk , I tried that , but I am getting duplicated value.. I updated the question with duplication — Mia Mia, Jul 20 '21 at 16:06
You have to re-read the link I posted earlier, and pay more attention. (There is an aggregate function missing in your SQL statement (actually more than one).) — Luuk, Jul 20 '21 at 16:14
@Luuk Thankyou. .. :) I got my issue.. issue in group by clause. — Mia Mia, Jul 20 '21 at 16:45

score 0 · Accepted Answer · answered Jul 20 '21 at 17:23

I got the answer.

SELECT  id,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Household'
                 THEN answer.text
                 ELSE NULL 
             END
         ) AS Household,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Dependents' 
                 THEN answer.text
                 ELSE NULL 
             END
         ) AS Dependents,
         GROUP_CONCAT(
             CASE 
                 WHEN question.question_name = 'Generation'
                 THEN answer.text
                ELSE NULL 
             END
         ) AS Generation
       
    FROM user_answers
    inner join answer on user_answers.answer_id=answer.answer_id
    inner join question on answer.question_id=question.question_id
    GROUP BY id

You also can check out the [answers](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) of this one containing dynamic approaches as the title has, where no need to repeat each cases of `question_name` column. — Barbaros Özhan, Jul 22 '21 at 13:55

Implement Dynamic pivot in MYSQL

1 Answers1