PostgreSQL: Put the values in one column as header in result set?

Question

Let's say I have two tables, one with schools and one with classes. Both tables have SchoolID so they can be matched. They look something like this:

TableSchools

SchoolID SchoolName
1        SchoolOne
2        SchoolTwo
3        SchoolThree

TableClasses

ClassID SchoolID ClassName
1       1        ClassOne
2       1        ClassTwo
3       2        ClassThree
4       2        ClassFour
5       2        ClassFive
6       3        ClassSix
7       3        ClassSeven

and I want my resultset to be like this

SchoolOne   SchoolTwo   SchoolThree
ClassOne    ClassThree  ClassSix
ClassTwo    ClassFour   ClassSeven
NULL        ClassFive   NULL

I've heared in TSQL I could use group by with rollup but it seems that's not possible or at least I don't understand how to use it from here.

You are not looking for rollup you are looking for a "crosstab" aka "pivot". — user330315
– user330315, Commented Sep 21, 2016 at 11:24
I've checked out the PostgreSQL documentation regarding crosstab and it seems to me like I have to name the column names/headers manually when using it. That doesn't really cut it for me as the amount and the names of the schools is flexible. Otherwise I could also simply use column aliases. Or am I missing something major? — Jdv
– Jdv, Commented Sep 21, 2016 at 11:32
The third column name in your desired result is SchoolThree. As there are no data like this in TableSchools, I'm thinking: is this a typo or the names should be sequential, even not matching the SchoolNames in TableSchools? — Felypp Oliveira
– Felypp Oliveira, Commented Sep 21, 2016 at 11:34
Things like that are much easier implemented in your application. You can't define column names dynamically based on the result in a SQL statement. — user330315
– user330315, Commented Sep 21, 2016 at 11:37
@FelyppOliveira it was a typo, fixed. Thanks for the heads up! a_horse_with_no_name I guess that seals the deal, thanks tho. — Jdv
– Jdv, Commented Sep 21, 2016 at 11:39

Joe Love · Accepted Answer · 2016-09-21 14:58:02Z

1

Not EXACTLY a pivot, but should allow the same programatic results in your code:

select ts.schoolName 
, array_agg(tc.className order by tc.className) vals
from tableSchools ts
join tableClasses tc tc.schoolID=ts.schoolID
group by ts,schoolName

Note: Depending on your client, it might be easier to return JSON instead of array.. let me know if you need help with that part and I'll update.

answered Sep 21, 2016 at 14:58

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Put the values in one column as header in result set?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related