SQL query how to show column based on data from a different column?

Question

I have this query:

SELECT Students.StudentNumber,
       Students.StudentSurname,
       Students.StudentFirstNames,
       Students.SchoolYear,
       Students.Class,
       Cycle,
       Section,
       MarksEntry.SubjectCode,
       MarksEntry.AssessmentPeriod,
       MarksEntry.SubjectMaxima,
       MarksObtained,
       Subject.SubjectName
FROM Students,
     MarksEntry,
     Subject
WHERE Students.StudentNumber = MarksEntry.StudentNumber
  AND MarksEntry.SubjectCode = Subject.SubjectCode
  AND Students.Class = MarksEntry.Class
  AND MarksEntry.SchoolYear = '2020-2021'
  AND MarksEntry.Class = '1ere LIT'
  AND MarksEntry.AssessmentPeriod = '2è P'
ORDER BY Students.StudentSurname;

I get the results this way:

How can I modify this query so that I can get the data listing only the students with no duplication and the score marks under each subject, with the name of the subject as column name. The subjects names are not the same, they differ from classes, so they have to be read dynamically from the subjectName column based on the query condition. Something like this:

Answer 1

You can use LEFT JOIN with the table MarksEntry multiple times, where each MarksEntry corresponds to a different subject.

For example,

SELECT s.StudentNumber,
       rel.MarksObtained ReligionMarks,
       bio.MarksObtained BiologyMarks
FROM Students s
LEFT JOIN MarksEntry rel 
    ON s.StudentNumber = rel.StudentNumber 
    AND rel.SubjectName = 'Religion'
LEFT JOIN MarksEntry bio 
    ON s.StudentNumber = bio.StudentNumber 
    AND bio.SubjectName = 'Biology'

This will only work if there aren't any duplicated students in the same course, otherwise, each register will appear multiple times.

I'd also recommend you take a look at the GROUP BY clause in case there are duplicates