Postgresql pivot row data without aggregate function

Question

I have this table:

create table student
(
    key serial primary key,
    id int, 
    type_name text,
    updated_by text,
    updated date, 
    text_data text,
    int_data int
);

Sample table and data is here

A student id may have multiple rows(marks) for same subject. I would need to pivot all marks of each students for each subject.

I'm trying to pivot data with below query

select 
    id,
    max(updated_by) as updated_by,
    max(updated) as updated,
    max(case when type_name='Name' then text_data end) as "Name",
    max(case when type_name='Math' then int_data end) as "Math",
    max(case when type_name='English' then int_data end) as "English",
    max(case when type_name='Social' then int_data end) as "Social",
    max(case when type_name='Science' then int_data end) as "Science"
from
    stud 
group by 
    id

But this is not giving all marks for id 1 and in Math subject has marks 8 and 5. since i used max function in my query, i'm getting single value.

But it seems without aggregate function we can't do group_by.

Is there way in Postgresql to pivot data without aggregate functions to get below expected output

Thanks

Barbaros Özhan · Accepted Answer · 2022-04-27 18:19:51Z

1

What you need as another aggregation, namely STRING_AGG() along with FILTER clause such as

SELECT id,
       MAX(text_data) AS "Name",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Math') AS "Math",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='English') AS "English",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Social') AS "Social",
       STRING_AGG ( int_data::TEXT, ',' ORDER BY key ) FILTER (WHERE type_name='Science') AS "Science"
  FROM stud 
 GROUP BY id

Demo

answered Apr 27, 2022 at 18:19

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

Postgresql pivot row data without aggregate function

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