MySQL Dynamic Column Selection – How to Select Columns Dynamically in MySQL

MySQL

I have following code, for this, I have two tables, one table contains questions and another table contain answers.
answers table something like following : (ans_question_1 to ans_question_95)

| answersId |ans_question_1| ans_question_2| student_id
|-----------|--------------|---------------|-----------
| 1         | Male         | 23            |11
| 2         | Female       | 21            |12

Questions table (questions 1 to 95)

| question_id | Question| 
|-------------|---------|  
| 1           | Gender       
| 2           | Area code

Want table something like this (Do not want manual code like 'left join')

| student_id  | Question  | Answer
|-------------|-----------|-------
| 11          | Gender    | Male
| 11          | Area code | 23
| 12          | Gender    | Female
| 12          | Area code | 21

i tried following code:

SELECT DISTINCT q.`question_id`,
CONCAT("ans_question_", q.question_id),
    (
        SELECT (CONCAT("ans_question_", q.question_id))
        FROM `answers` a
        WHERE REPLACE(CONCAT("ans_question_", q.question_id), 'ans_question_', '')=q.`question_id`
    ) AS answer
FROM questions q, options_type o
WHERE o.`options_type_id`=q.`questions_type_id`
AND q.questions_status=1

SELECT (CONCAT("ans_question_", q.question_id)) i tried to get column value of the answer table, but its printing column name, not value, if somehow I can extract value by this code then my problem could solve if anyone can help.
Thanks

Answer something Like :

| student_id| Gender| Area code| Other question
|-----------|-------|----------|---------------
| 11        | Male  | 23       |Other answers
| 12        | Female| 21       |Other answers

Best Answer

You need to UNPIVOT the current result.

I've set up the next example:

create table if not exists questions (question_id int, question varchar(20));
create table if not exists answers (student_id int, answer_id int, ans_question_1 varchar(20), ans_question_2 varchar(29));

insert into questions values (1,'Gender'),(2,'Area code');
insert into answers values (11,1,'Male','23'),(12,2,'Female','21');

This solution uses a CROSS JOIN with the questions table and it assumes that exist one question_id for each ans_question_xx.

Important

It must exists a question_id = 1 and one ans_question_1 column, that belong s to this question.

select     student_id, aq.question,
           case aq.question_id
                when 1 then ans_question_1
                when 2 then ans_question_2
           end Answer
from       answers
cross join (select question_id, question from questions) aq
order by   student_id, aq.question_id;                                          

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using a dynamic query

First build the whole 'when xx then ans_question_xx' using group_concat().

select @caseWhen := group_concat(concat('when ', 
                                        cast(question_id as char(20)), 
                                        ' then ans_question_', 
                                        cast(question_id as char(20))) separator ' ') 
from questions;

when 1 then ans_question_1 when 2 then ans_question_2

Then build the sql query sentence:

set @sql = concat('select student_id, aq.question, case aq.question_id ', 
                  @caseWhen, 
                  ' end Answer from   answers
                    cross join (select question_id, question from questions) aq
                    order by student_id, aq.question_id;');

Finally execute it:

PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

The result is the same:

| student_id | question  | Answer |
|------------|-----------|--------|
| 11         | Gender    | Male   |
| 11         | Area code | 23     |
| 12         | Gender    | Female |
| 12         | Area code | 21     |

Rextester here

Using an stored procedure

You can use previous dynamic query within an stored procedure:

create procedure getStudentAnswers(StudentId int)
begin

    set @caseWhen := (select group_concat(concat('when ', 
                                        cast(question_id as char(20)), 
                                        ' then ans_question_', 
                                        cast(question_id as char(20))) separator ' ') 
                     from questions);                           

    set @sql = concat('select student_id, aq.question, case aq.question_id ', 
                      @caseWhen, 
                      ' end Answer from   answers
                        cross join (select question_id, question from questions) aq
                        where student_id = ', cast(StudentId as char(20)), 
                       ' order by student_id, aq.question_id;');

    PREPARE dynamic_statement FROM @sql;
    EXECUTE dynamic_statement;
    set @sql := null;
    set @caseWhen := null;
    DEALLOCATE PREPARE dynamic_statement;

end \\

call getStudentAnswers(11);

call getStudentAnswers(12);

Rextester here

Related Question