0

I have a table that records answers to questions that are answered multiple times per year. The table has a columns for the user ID, the year they took the question, the month they took the question, the subject and the question number

ID  | Year | Month | Subject | Q number| Answer |
-------------------------------------------------
1   | 2016 | 01    |  01     | 1       | Yes    |
1   | 2016 | 01    |  01     | 2       | Yes    |
1   | 2016 | 01    |  01     | 3       | no     |
1   | 2016 | 01    |  02     | 1       | Yes    |
1   | 2016 | 01    |  02     | 1       | Yes    |
1   | 2016 | 01    |  02     | 1       | Yes    |
1   | 2016 | 02    |  01     | 1       | Yes    |
1   | 2016 | 02    |  01     | 1       | Yes    |
1   | 2016 | 02    |  01     | 2       | Yes    |
1   | 2016 | 02    |  01     | 3       | Yes    |
1   | 2016 | 02    |  02     | 1       | Yes    |
1   | 2016 | 02    |  02     | 1       | Yes    |
1   | 2016 | 02    |  02     | 1       | Yes    |
2   | 2016 | 01    |  01     | 1       | Yes    |
2   | 2016 | 01    |  01     | 2       | no     |
2   | 2016 | 01    |  01     | 3       | no     |etc

I want to create a query that returns one row per user with a column for each answer.

ID | 2016-01_sub01_Q01 | 2016-01_sub01_Q02 | 2016-01_sub01_Q03 | etc
----------------------------------------------------------------------
1  | yes               | yes               | no                |  etc  
2  | yes               | no                | no                |  etc

I've tried multiple left joins onto the results table matching for each answer but the query is still running and thats just with 2 subjects (out of 13+) and 9 qs per subject.

select u.userid, 
    Y2016_Mon2_Sub01_Q1.answer,
    Y2016_Mon2_Sub01_Q2.answer,
    Y2016_Mon2_Sub01_Q3.answer,

from users u 
left join answers Y2016_Mon2_Sub01_Q1 on ( 
            answers.userid = users.userid 
            and Y2016_Mon2_Sub01_Q1.year = 2016 
            and Y2016_Mon2_Sub01_Q1.mon = 02 
            and Y2016_Mon2_Sub01_Q1.sub = 01 
            and Y2016_Mon2_Sub01_Q1.question = 1) 
left join answers Y2016_Mon2_Sub01_Q2 on ( 
            answers.userid = users.userid 
            and Y2016_Mon2_Sub01_Q1.year = 2016 
            and Y2016_Mon2_Sub01_Q1.mon = 02 
            and Y2016_Mon2_Sub01_Q1.sub = 01 
            and Y2016_Mon2_Sub01_Q1.question = 2) 
etc

But as you can imagine that's becoming an issue to manage and with all subjects and questions I'd end up with 100's joins. I've tried searching multiple results to one row but not found anything yet... Any clues/ examples appreciated. I keep finding group_concat but I need the answers in their own columns.

slodeveloper
  • 242
  • 3
  • 21
Curious User
  • 39
  • 1
  • 6
  • You better do that in php maybe – Yuri Apr 08 '16 at 14:36
  • yeah, when I rewrite the query to get the data to then do the "One line per user in php" the query takes <1 sec. But I've had this type of challenge several times before and always thought... "can I do this just in sql?" but never got round to asking – Curious User Apr 08 '16 at 15:03
  • Try this if I understood your question: http://stackoverflow.com/questions/7674786/mysql-pivot-table – vaso123 Apr 08 '16 at 15:17
  • that's not quite it as it sums / counts the number of occurrences. I just want to get all of each users answers on one row, with a column per question – Curious User Apr 08 '16 at 15:29
  • The PHP solution is simpler and more easily scalable. – Strawberry Apr 08 '16 at 15:59

0 Answers0