How do I consolidate this table?-CodePudding

I have a problem that I will try to describe like this. I have a table in PostgreSQL like below (here's what I have).

Now I'm wrapping my head around how to "merge" or "consolidate" this table to make it look like this one on -> Here's what I want to have.

Multiple rows are the result of having different ID or different value in any column after in general (but I don't need that information anymore, so I may get rid of it without any consequences).

Is there any function or any trick that might bring me desired result?

What I have tried:

select "name"
       , "array_agg" [1][1] as math_grade 
       , "array_agg" [2][2] as history_grade 
       , "array_agg" [3][3] as geography_grade 
from (select "name"
             , array_agg(array[math_grade,history_grade,geography_grade]) 
      from temp1234 
      group by "name") as abc

Here is a example table:

create table temp1234 (id int
                       , name varchar(50)
                       , math_grade int
                       , history_grade int
                       , geography_grade int)

And example data:

insert into temp1234 values (1, 'John Smith', 3, null, null)
insert into temp1234 values (2, 'John Smith', null, 4, null)
insert into temp1234 values (3, 'John Smith', null, null, 3)

Best Regards

CodePudding user response：

This will give you what you want but I am sure that with more data you will find this query is not covering all you need ? Please do provide more data for more detailed help.

select min(id), name, max(math_grade), max(history_grade), max(geography_grade)
from temp1234
group by name

Here is a demo