SQL (MariaDB) join 3 tables and pivot-CodePudding

I have 3 MySQL (MariaDB) tables:

Students:

student_id	name	result_id
1	Badui	31
2	Tama	32
3	Rombeng	33

Subject:

subject_id	points	result_id
1	90	31
2	90	31
3	90	31
1	84	32
2	85	32
3	86	32
1	87	33
2	89	33
3	88	33

Average_Score:

AS_id	average_score	result_id
1	90	31
2	85	32
3	88	33

Desired Table:

name	1	2	3	average_score
Badui	90	90	90	90
Rombeng	87	89	88	88
Tama	84	85	86	85

I need to have this view by using join and pivot, but I have no idea in combining the join and pivot altogether. This is my sql of JOIN

SELECT 
  name, subject_id, points, average_score 
FROM 
  Students as stud
JOIN 
  Average_Score as avr  
ON  
  stud.result_id = avr.result_id
JOIN
  Subject as subj
ON
  avr.result_id = subj.result_id
ORDER BY name

and here is the result of the JOIN sql

name	subject_id	points	average_score
Badui	1	90	90
Badui	2	90	90
Badui	3	90	90
Rombeng	1	87	88
Rombeng	2	89	88
Rombeng	3	88	88
Tama	1	84	85
Tama	2	85	85
Tama	3	86	85

Is there any efficient way to modify my sql to match the pivot table to get the desired view?

I need some helps to have the correct query, please. Thank you.

CodePudding user response：

You can try using the following query:

SELECT stud.name,
       MAX(IF(subj.subject_id=1, subj.points, NULL)) AS `1`,
       MAX(IF(subj.subject_id=2, subj.points, NULL)) AS `2`,
       MAX(IF(subj.subject_id=3, subj.points, NULL)) AS `3`,
       ROUND(AVG(subj.points))                       AS `average_score`
FROM       Students stud
INNER JOIN Subject  subj
        ON stud.result_id = subj.result_id
GROUP BY stud.name

The idea behind this solution is to join the tables Students and Subject on the result_id field, then get a column for each points corresponding to the related subject_id and aggregate with the MAX function to remove the NULL values. Eventually, instead of joining with the Average_Points table, you can just compute right away from the data you have in the Subject table.

Try it here.