Mysql – Dynamic column value to columns name theSQL

MySQL

I have table like following, i have almost 100+ questions and more than 30,000 students:

StudentId | Questions  | QID| Answer  | View   | 
1         | YourGender | 1  | Male    | Gender | 
2         | YourGender | 1  | Female  | Gender |
1         | YourAge?   | 2  | 23      | Age    | 
2         | YourAge    | 2  | 21      | Age    |

I need table like followings:

StudentId | YourGender  | YourAge?
    1     | Male        | 23  
    2     | Female      | 21

My questions are randomly add/modify/del, so I want everything should work dynamically, when questions added or deleted I need not to change anything on my code.

Best Answer

You can solve it on this way:

Select all distinct StudentId, and join its with two subqueries, one with all answers about Gender and another with all answers about Age, both grouped by StudentId.

select    distinct q.StudentId,
          g.Answer as YourGender,
          a.Answer as YourAge
from      questions q
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Gender'
           group by StudentId
          ) g
on         g.StudentId = q.StudentId
left join (
           select   StudentId, Answer
           from     questions
           where    View = 'Age'
           group by StudentId
          ) a
on         a.StudentId = q.StudentId
;

This is the result:

| StudentId | YourGender | YourAge |
|-----------|------------|---------|
| 1         | Male       | 23      |
| 2         | Female     | 21      |

Can check it here: http://rextester.com/XPNAT23300

Related Question