How to return multiple row value as a single row with multiple column using sql

Question

There are three row in Static Table called 'SUBJECT'

SubjectID       SubjectCode
---------------------------
    1           MATHS
    2           SCIENCE
    3           ENGLISH
---------------------------

My application will put entry of each subject marks for corresponding students in another table called 'MARKS'

MarkID      StudentID       SubjectID       Marks
---------------------------------------------------
    1           1               1           90
    2           1               2           85
    3           1               3           80
    4           2               1           100
    5           2               2           70
    6           2               3           90

I am getting struggle to create a virtual table as like below:

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          85          80
    2           100         70          90

I tried with the query

Select  MARKS.StudentID,
        CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.Marks END as Maths, 
        CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.Marks END as Science, 
        CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.Marks END as English, 
        FROM 
        MARKS 
        JOIN SUBJECTS on SUBJECTS.SubjectID = MARKS.SubjectID
        GROUP BY 
        MARKS.StudentID, SUBJECTS.SubjectCode, MARKS.Marks

But it returns,

StudentID       Maths       Science     English 
------------------------------------------------
    1           90          NULL        NULL
    1           NULL        85          NULL
    1           NULL        NULL        80
    2           100         NULL        NULL
    2           NULL        70          NULL
    2           NULL        NULL        90

Answer 1

You are close. You just need aggregation:

SELECT MARKS.StudentID,
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN MARKS.MARK END) as Maths, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN MARKS.MARK END) as Science, 
       MAX(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN MARKS.MARK END) as English, 
FROM MARKS JOIN
     SUBJECTS 
     ON SUBJECTS.SubjectID = MARKS.SubjectID
GROUP BY MARKS.StudentID
ORDER BY MARKS.StudentID;

I would recommend that you use table aliases, so the query is easier to write and read:

SELECT m.StudentID,
       MAX(CASE WHEN s.SubjectCode = 'MATHS' THEN M.MARK END) as Maths, 
       MAX(CASE WHEN s.SubjectCode = 'SCIENCE' THEN M.MARK END) as Science, 
       MAX(CASE WHEN s.SubjectCode = 'ENGLISH' THEN M.MARK END) as English, 
FROM MARKS m JOIN
     SUBJECTS s
     ON s.SubjectID = m.SubjectID
GROUP BY m.StudentID
ORDER BY m.StudentID;

Answer 2

Use group by and aggregation

Select  MARKS.StudentID,
        max(CASE WHEN SUBJECTS.SubjectCode = 'MATHS' THEN RS.AgentPercentage END) as Maths, 
        max(CASE WHEN SUBJECTS.SubjectCode = 'SCIENCE' THEN RS.AgentPercentage END) as Science, 
        max(CASE WHEN SUBJECTS.SubjectCode = 'ENGLISH' THEN RS.AgentPercentage END) as English, 
        FROM 
        MARKS 
        JOIN SUBJECTS on SUBJECTS.SubjectID = MARKS.SubjectID
group by MARKS.StudentID