Generate columns from values returned by SELECT

Question

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.

S-Man · Accepted Answer · 2021-01-28T09:44:28.247

1

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, 
    ...

edited Jan 28 '21 at 09:44

answered Jan 28 '21 at 09:36

S-Man

22,521
7
40
63

Apologies: I might not have made it clear (I'll edit my question), 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. – Luke Carr Jan 28 '21 at 09:47
That's, unfortunately, not simply achievable. You always need to know the returning columns. Dynamic columns are not possible in SQL (unless you do deep internal magic) – S-Man Jan 28 '21 at 09:49
1

Here's a great post on how to achieve dynamic columns, but, as said, it's not simply done: https://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by – S-Man Jan 28 '21 at 09:50
Hmm, thanks for sharing the other post. Unfortunately, I can't really afford the time to go digging into fairly complex queries, so I think pulling the data out into something like Python and transforming it is going to be much better as a temporary solution. – Luke Carr Jan 28 '21 at 09:53
That would be the better way, yes :) – S-Man Jan 28 '21 at 09:55

score 1 · Answer 2 · answered Jan 28 '21 at 09:37

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

Generate columns from values returned by SELECT

2 Answers2