Case statement in SQL

#tabular #sql

I was recently trying into make the relational columns to rows. I came across case statement.

The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Example below shows how to flatten subject name columns to rows with generic subject...

id name english maths science
123 Harry 90 75 85

345 Porter 70 80 90

Now the below query can transpose columns to row using subject_sequence table's rows.
SELECT
id,name,
CASE seq_id
WHEN 1 THEN 'english'
WHEN 2 THEN 'maths'
WHEN 3 THEN 'science'
END AS subject,
CASE seq_id
WHEN 1 THEN english
WHEN 2 THEN maths
WHEN 3 THEN science
END AS marks
FROM student CROSS JOIN subject_sequence;

Output:
id name subject marks

123 Harry english 90
123 Harry maths 75
123 Harry science 85
345 Porter english 70
345 Porter maths 80
345 Porter science 90