Mysql – Select a table with multiple columns according to IN clause

MySQL

I've a query

SELECT t1.user,t1.answer as 'Q1',t2.answer as 'Q2'
from 
(select answer,user from survey_answer where question_id=45) t1
JOIN
(select answer,user from survey_answer where question_id=46) t2
ON
t1.user=t2.user

In this the question_id i.e. 45,46,47,48,…etc. is in hundreds
the number on join increases number of columns as q1,q2,q3… so on…

like : 

________________________
user | q1 | q2 | q4 | q5
------------------------
1    | ok | yes|    | no
------------------------
2    |    | yes| no |

Is there a way to form the query using IN(45,46,47,….) or use loop in procedure/function…

Best Answer

If you don't need a column for each answer you can use group_concat() to build a single string with all answers per user.

drop table if exists survey_answer;
create table if not exists survey_answer(user int, question_id int, answer varchar(200));
insert into survey_answer values
(1,60,'answer 1'),
(1,61,'answer 2'),
(1,62,'answer 3'),
(2,61,'answer 4'),
(2,52,'answer 5'),
(3,40,'answer 6'),
(3,62,'answer 7'),
(4,61,'answer 8'),
(4,62,'answer 9'),
(4,10,'answer 10'),
(4,32,'answer 11');

select distinct(t1.user), 
       (select group_concat(t2.answer, ',')
        from survey_answer t2
        where t2.user = t1.user) answers
from survey_answer t1
;

drop table if exists survey_answer;

This is the result:

| user | answers                                   |
|------|-------------------------------------------|
| 1    | answer 1,,answer 2,,answer 3,             |
| 2    | answer 4,,answer 5,                       |
| 3    | answer 6,,answer 7,                       |
| 4    | answer 8,,answer 9,,answer 10,,answer 11, |

Rextester here

Related Question