SQL query - stumped me during an interview

Question

I was asked a basic SQL question during an interview and I was stumped by it. I couldnt come up with a solution. The table structure and data are given below :

Student ID	Subject	Marks
1	Maths	20
1	Bio	30
2	Maths	40
2	Bio	50
3	Maths	60
3	Bio	70

The output should be :

Student ID	Maths	Bio
1	20	30
2	40	50
3	60	70

What will be the query to get the desired output?

Answer 1

SELECT Student_id, t1.mark Maths, t2.Mark Bio
FROM table t1
JOIN table t2 USING (Student_id)
WHERE t1.Subject = 'Maths'
  AND t2.Subject = 'Bio'

For dynamic subjects list use, for example, https://stackoverflow.com/a/66136355/10138734

Answer 2

You can do this with group by and conditional sums:

SELECT `Student ID`, SUM(CASE Subject WHEN 'Maths' THEN Marks END) Maths,
                     SUM(CASE Subject WHEN 'Bio'   THEN Marks END) Bio
FROM   mytable
GROUP BY `Student ID`;