0

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
  • Please add your (reduced) table schema. – McNets Apr 22 '17 at 10:40
  • (reduced) table schema? – Shimul Chowdhury Apr 22 '17 at 10:55
  • Do not show 96 ans_question_xx columns – McNets Apr 22 '17 at 10:57
  • is there any way to convert this (CONCAT("ans_question_", q.question_id)) to table column name? – Shimul Chowdhury Apr 22 '17 at 11:01
  • You should build a Dynamic query: blog.sqlauthority.com/2014/06/20/… – McNets Apr 22 '17 at 11:02
1

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

  • thanks for your suggestion....is there any way if i want to put it in stored procedure? and pass a single student id to get only one student information? – Shimul Chowdhury Apr 22 '17 at 12:43
  • sure, but now I'm on mobile till tomorrow evening. I'll try it tomorrow – McNets Apr 22 '17 at 18:10
  • Can you help me that, if any questions dosent have any answer (value or null) that should be in the result? for example, if student id 11 dosent have answer area code, then student_id and Answer columns should be null and questions will remain as it is. like | NULL | Area code | NULL | – Shimul Chowdhury Apr 23 '17 at 17:28
  • Similar to this? rextester.com/XNX42341 – McNets Apr 23 '17 at 17:52
  • yes thanks, Can i replace answers table column name to original questions from question table? for example, my answer table has column name ans_questions_01, i want my answer table with table column name 'Gender' etc? – Shimul Chowdhury Apr 24 '17 at 1:48

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.