Joining rows in same table - postgres

Question

I have a table similar to the following -

id	name	subject	score
1	XYZ	English	50
1	XYZ	Math	30

Now I need to something as follows-

id	name	English	Math
1	XYZ	50	30

I tried UNION and JOIN but nothing worked.

Answer 1

After I fixed the example in the question to make sense, this would produce your desired result:

SELECT *
FROM   crosstab(
   'SELECT id, name, subject, score
    FROM   tbl
    ORDER  BY 1,3'  -- just "ORDER BY 1" works, too
  , $$VALUES ('Math'::text), ('English')$$
   ) AS ct (id int, name text, "Math" int, "English" int);

fiddle

Detailed explanation:

• PostgreSQL Crosstab Query

In particular, to add "extra" columns (name in this example):

• Pivot on Multiple Columns using Tablefunc