CODEWITHSUNDEEP
sqlpostgresqlselect
Luke Carr
Luke Carr

Reputation: 571

Generate columns from values returned by SELECT

I've got a query that returns data like so:

student course grade
a-student ENG-W05 100
a-student MAT-W05 85
a-student ENG-W06 100
b-student MAT-W05 90
b-student SCI-W05 75

The data is grouped by student and course. Ideally, I'd like to have the above data transformed into the below:

student ENG-W05 MAT-W05 ENG-W06 SCI-W05
a-student 100 85 100 NULL
b-student NULL 90 NULL 75

So, after the transformation, each student only has one record, with all of their grades (and any missing courses graded as null).

Does anyone have any ideas? Obviously, this is fairly simple to do if I take the data out and transform it in a language (like Python), but I'd love to get the data in the desired format with an SQL query.

Also, would it be possible to have the columns order alphabetically (ascending)? So, the final output would be:

student ENG-W05 ENG-W06 MAT-W05 SCI-W05
a-student 100 100 85 NULL
b-student NULL NULL 90 75

EDIT: To clarify, the values in course aren't known. The ones I provided are just examples. So ideally, if more course values found there way into that first query result (the first table), they would still be mapped to columns in the final result (without needing to change the query). In reality, I actually have >1k distinct values for the course column, and so I can't manually write out each one.

Upvotes: 1

Views: 43

Answers (2)

S-Man
S-Man

Reputation: 23686

demos:db<>fiddle

You can use conditional aggregation for that:

SELECT
    student,
    SUM(grade) FILTER (WHERE course = 'ENG-W05') as eng_w05, 
    SUM(grade) FILTER (WHERE course = 'MAT-W05') as mat_w05, 
    SUM(grade) FILTER (WHERE course = 'ENG-W06') as eng_w06, 
    SUM(grade) FILTER (WHERE course = 'SCI-W05') as sci_w05
FROM mytable
GROUP BY student

The FILTER clause allows to aggregate only some specific records. So this one aggregates all records for a specific course.

Finding the correct aggregate function could be difficult. Here SUM() does the job, as there's only one value per group. MAX() or MIN() would do it as well. It depends on your real requirement. If there's really only one value per group, it doesn't matter, you just need to do any aggregation.

Instead of FILTER clause, which is Postgres specific, you could use the more SQL standard fitting CASE clause:

SELECT
    student,
    SUM(
       CASE 
           WHEN course = 'ENG-W05' THEN grade
       END
    ) AS eng_w05, 
    ...

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can use the conditional aggregation as follows:

select student, 
       max(case when course = 'ENG-W05' then grade end) as "ENG-W05",
       max(case when course = 'MAT-W05' then grade end) as "MAT-W05",
       max(case when course = 'ENG-W06' then grade end) as "ENG-W06",
       max(case when course = 'SCI-W05' then grade end) as "SCI-W05"
  from (your_query) t
 group by student

Upvotes: 1

Related Questions