Change column values to column headers in Postgres SQL replacing with values from another column

Question

I am unable to figure out how to make a column values into column headers and assign appropriate values as it happens

Say I have a Postgres database with the following table:

 Name      Subject       Score      Region
=======   =========     =======     =======
 Joe       Chemistry      20        America 
 Robert    Math           30        Europe
 Jason     Physics        50        Europe
 Joe       Math           70        America
 Robert    Physics        80        Europe
 Jason     Math           40        Europe
 Jason     Chemistry      60        Europe

I want to select/fetch data in the following form:

 Name      Chemistry     Math       Physics     Region
=======   ==========    =======    ========    ========
 Joe        20            70         null       America
 Robert     null          30         80         Europe
 Jason      60            40         50         Europe

Considering that there are 80 subjects. How do I write an SQL select statement that returns data in this format?

https://stackoverflow.com/a/20618487/11878472 check this solution — Tejas, Jul 31 '21 at 18:58

score 0 · Answer 1 · answered Jul 31 '21 at 21:33

In Postgres, I recommend using the FILTER syntax for conditional aggregation:

SELECT name,
       MAX(score) FILTER (WHERE subject = 'Chemistry') AS Chemistry,
       MAX(score) FILTER (WHERE subject = 'Math') AS Math,
       MAX(score) FILTER (WHERE subject = 'Physics') AS Physics
FROM grades
GROUP BY name

Change column values to column headers in Postgres SQL replacing with values from another column

1 Answers1