How to transpose groups of rows to columns

Question

i have the following table structure:

NAME    SUBJECT  LEVEL      RESULT
Smith   maths    beginner   C
Miller  maths    pro        B
Prince  maths    beginner   F
Smith   physics  pro        B
Miller  physics  pro        B
Prince  physics  beginner   E

And I want some kind of transposing, so that the result would look like this:

NAME    LEVEL_maths  RESULT_maths  LEVEL_physics   RESULT_physics
Smith   beginner     C             pro             B
Miller  pro          B             pro             B
...

Any idea how this can be done with (Postgre)SQL? Any hint is appreciated.

Answer 1

try this:

select name, 
max(case when subject='maths' then level else end)level_maths,
max(case when subject='maths' then RESULT else end)RESULT_maths,
max(case when subject='physics' then level else end)level_physics,
max(case when subject='physics' then RESULT else end)RESULT_physics
from test
group by name;