Group the rows of same name of a column

Question

INPUT:

Student_id subject mark
1 math 88
1 phy. 67
1 che. 86
2 math 89
2 phy. 97
2 che. 85
3 math 68
3 phy. 80
3 che. 70

OUTPUT:

Student_id math phy che
1 88 67 86
2 89 97 85
3 68 80 70

Answer 1

You may use conditional aggregation grouped by student_id as the following:

SELECT Student_id,
       MAX(CASE subject WHEN 'math' THEN mark END) math,
       MAX(CASE subject WHEN 'phy.' THEN mark END) phy,
       MAX(CASE subject WHEN 'che.' THEN mark END) che
FROM table_name
GROUP BY Student_id
ORDER BY Student_id

See a demo.