I am having the following table

This table contains information about the marks scored by students in various subject.

Now I need to find the maximum mark scored by each student among the various subject

I need the output to be like

CodePudding user response：

This would need a lateral join with the list of subject/mark from the corresponding columns. This works for any number of Subject X columns.

select ext."Student Id", l.* 
from the_table ext, lateral
(
   select l.key, l.value::numeric 
   from the_table t, lateral json_each_text(to_json(t)) l(key, value)
   where "Student Id" = ext."Student Id" and l.key like 'Subject%'
   order by l.value::numeric desc limit 1
) l;

DB-Fiddle demo