CODEWITHSUNDEEP
sqlpostgresqlgroup-bypivot
vishwas-trivedi
vishwas-trivedi

Reputation: 594

Merge rows with same in table postgresql

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?

Upvotes: 1

Views: 132

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 1

GMB
GMB

Reputation: 222462

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

Upvotes: 1

Related Questions