Merge rows with same in table postgresql

Question

I have following table in postgresql:

id | grade | comment
--------------------
01 | A     | Good
01 | B     | OK
01 | C     | BAD
02 | A     | Good
02 | E     | Very BAD
02 | D     | BAD

I want to fetch records in following format

id | grade1 | grade2 | grade3 | comment1 | comment2 | comment3  
--------------------------------------------------------------
01 | A      | B      | C      | Good     | OK       | BAD
02 | A      | E      | D      | Good     | Very BAD | BAD

Can anyone help?

GMB · Accepted Answer · 2020-03-20 09:54:21Z

1

You can use row_number() and conditional aggregation:

select 
    id,
    max(grade)   filter(where rn = 1) grade1,
    max(grade)   filter(where rn = 2) grade2,
    max(grade)   filter(where rn = 3) grade3,
    max(comment) filter(where rn = 1) comment1,
    max(comment) filter(where rn = 2) comment2,
    max(comment) filter(where rn = 3) comment3
from (
    select t.*, row_number() over(partition by id order by grade) rn
    from mytable t
) t
group by id

answered Mar 20, 2020 at 9:54

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

vishwas-trivedi Over a year ago

Thanks, could you give this to me in fiddler?

Gordon Linoff · Accepted Answer · 2020-03-20 10:55:39Z

SQL tables represent unordered sets. Your results seem to imply that the results are in some order. No column contains that ordering.

One method is to just put the values in arrays:

select id, array_agg(grade order by <ordering col>) as grades,
       array_agg(comment order by ?) as comment
from t
group by id;

You can expand this to multiple columns:

select id,
       (array_agg(grade order by <ordering col>))[1] as grade_1,
       (array_agg(grade order by <ordering col>))[2] as grade_2,
       (array_agg(grade order by <ordering col>))[3] as grade_3
       (array_agg(comment order by <ordering col>)[1] as comment_1,
       (array_agg(comment order by <ordering col>)[2] as comment_2,
       (array_agg(comment order by <ordering col>)[3] as comment_3
from t
group by id;

Collectives™ on Stack Overflow

Merge rows with same in table postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related