MYSQL/Query: How to make table rows into column

Question

I have 3 tables tbl_contestant , tbl_criteria and tbl_judges. And then i have 1 more table combined this 3 table as my result, tbl_score.

tbl_criteria
------------------------
crit_id | criteria_name
   16   |    sports

tbl_judges
------------------------
judge_id  |   judge_name
   61     |    first
   62     |    second
   63     |    third


 tbl_cotestant
--------------------------------------
con_id  |  contestant_number | contestant_name |
   1    |         1          |      john       |
   2    |         2          |       sy        |
   3    |         3          |       Nah       |


tbl_score
--------------------------------------------------
score_id | crit_id | judge_id | contestant_number |   score
   1     |    16   |    61    |        1          |     25  
   2     |    16   |    61    |        2          |     25
   3     |    16   |    61    |        3          |     25
   4     |    16   |    62    |        1          |     25  
   5     |    16   |    62    |        2          |     73
   6     |    16   |    62    |        3          |     59
   7     |    16   |    63    |        1          |     70  
   8     |    16   |    63    |        2          |     80
   9     |    16   |    63    |        3          |     70

How can i achieve this output, judge_id row turns into column based on crit_id

contestant_number | contestant_name | 16_judge_61 | 16_judge_62 | 16_judge_63 | total
        1         |       john      |       25    |      25     |     70      |     
        2         |       sy        |       25    |      73     |     80      |
        3         |       Nah       |       25    |      59     |     70      |

Please correct my query

SELECT DISTINCT(c.contestant_number) , contestant_name , j1.sports as 
16_judge_61, j2.sports as 16_judge_62, j3.sports as 16_judge_63 from
tbl_criteria , tbl_score, tbl_contestant c 
LEFT JOIN tbl_ // <-- i have no idea how start from here joining those 4 tables together

You're sure you want to output to MySQL, as opposed to sort of presentation layer? — Strawberry, Sep 11 '16 at 09:43
This will not be possible using a 'plain' SQL query. Essentially you will need a means to 'pivot' rows to columns. See: http://stackoverflow.com/questions/1241178/mysql-rows-to-columns If you created and shared an SQL fiddle with test data at http://sqlfiddle.com/ you might get some more help with this. — Alan Hay, Sep 11 '16 at 10:07
@AlanHay Thanks, I think my previous question is wrong because tbl_score already constructed i can might get answer by doing the judge_id row turning into column — Echoing Throughout Tel Numara, Sep 11 '16 at 10:55

score 1 · Accepted Answer · answered Sep 11 '16 at 10:58

You could use CASE WHEN to solve this.

SELECT 
      s.contestant_number,
      c.contestant_name,
      SUM(CASE WHEN s.crit_id='16' AND s.judge_id='61' THEN s.score END) as 16_judge_61,
      SUM(CASE WHEN s.crit_id='16' AND s.judge_id='62' THEN s.score END) as 16_judge_62,
      SUM(CASE WHEN s.crit_id='16' AND s.judge_id='63' THEN s.score END) as 16_judge_63,
      SUM(s.score) as Total
    FROM tbl_score s
      INNER JOIN tbl_contestant c ON s.contestant_number = c.contestant_number
      GROUP BY s.contestant_number

see SQL Fiddle http://sqlfiddle.com/#!9/9efa5/1

Thanks really helpful – Echoing Throughout Tel Numara Sep 11 '16 at 11:19 — Echoing Throughout Tel Numara, Sep 11 '16 at 11:19

MYSQL/Query: How to make table rows into column

1 Answers1