MySQL Question

MYSQL/Query: How to make table rows into column

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

Answer

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

Comments