"How to query this in mysql? I am getting nowhere."

Question

I want to put all columns with different values on the right. Thank You.

(Column name as follows)
ID
Course
Year Level
Birthdate
Academic ID
Academic Year
Subject
Units
Grade  

1900000 | BSIS | 1st Year | 1997-10-08 | 2019-2020-1st Semester | MATH  | 3 | 2.0

1900000 | BSIS | 1st Year | 1997-10-08 | 2018-2019-1st Semester | ENG 1 | 3 | 2.1

1900000 | BSIS | 1st Year | 1997-10-08 | 2019-2020-1st Semester | FIL 1 | 3 | 2.0

1900000 | BSIS | 1st Year | 1997-10-08 | 2019-2020-2nd Semester | FIL 1 | 3 | 3.0

1900000 | BSIS | 1st Year | 1997-10-08 | 2018-2019-1st Semester | FIL 1 | 3 | 2.0

Done the best that i could. Cant get it.

Output: To One Row (For 2019-2020-1st Semester)

(Column name as follows)
ID
Course
Year Level
Birthdate
Academic ID
Academic Year
Subject
Units
Grade  
Subject2
Units
Grade
Subject3
Units
Grade
.......

1900000 | BSIS | 1st Year | 1997-10-08 | 2019-2020-1st Semester | MATH  | 3 | 2.0 | MATH | 3 | 2.0 | ENG 1 | 3 | 2.0 | FIL 1 | 3 | 2.0 |

Answer 1

This one should be straight forward, no need for dynamic columns and pivoting since there are only few subjects.

1. select unique ID using distinct() keyword.
2. join each subjects based on ID

select t1.ID, t1.Course, t1."Year Level", t1.Birthdate, t1."Academic ID", t1."Academic Year"
    ,m.Subject, m.Units, m.Grade
    ,e.Subject, e.Units, e.Grade
    ,e.Subject, e.Units, e.Grade
from
    (select distinct ID, Course, "Year Level", Birthdate, "Academic ID", "Academic Year" from tableA) as t1 
left join tableA m on Subject = 'MATH' and m.ID = t1.ID
left join tableA e on Subject = 'ENG 1' and e.ID = t1.ID
left join tableA f on Subject = 'FIL 1' and f.ID = t1.ID