return only 1 row

Question

I have 2 tables one contain students name and id students

StdID | StdName
  1   |  'John'

the seconed contain each subject result in a row

stdresults

StdID  | SubjectID | SubjectName | Degree
 1     |      1    |   'English' |  90
 1     |      2    |   'Math'    |  86

I want the result to be like this

StdID | StdName | Subject1Degree | Subject2Degree | Subject ... Degree
  1      'John'         90               86            ....

Whatever was the number of subject

When I make a join the result is in multi rows like this 1 john 90 1 john 86 1 john .. etc.. — Ahmed Badreldin Amado, Dec 28 '17 at 14:11
Check this https://vitobotta.com/2015/12/10/mysql-dynamic-column-name/ — , Dec 28 '17 at 14:12

score 0 · Answer 1 · answered Dec 28 '17 at 14:36

Try this:

SELECT T1.STDID,
       T1.STDNAME,
       SUM(CASE WHEN (T2.SUBJECTNAME = 'ENGLISH' AND T2.SUBJECTID = 1) THEN 
              T2.DEGREE  ELSE 0 END) AS SUBJECT1DEGREE,
       SUM(CASE WHEN (T2.SUBJECTNAME = 'MATH' AND T2.SUBJECTID = 2) THEN 
             T2.DEGREE ELSE 0 END)AS SUBJECT2DEGREE
FROM STUDENTS T1, STDRESULTS T2
GROUP BY T1.STDID;

Demo

return only 1 row

1 Answers1