Merge rows in postgresql with dynamic number of rows fitting the merge condition

Question

I have a table with many records. It has the following structure:

Table (col1, col2, col3, col4, col5, col6):

a b c val1  val2  val3
d e f val4  val5  val6
d e f val7  val8  val9
g h i val10 val11 val12
g h i val13 val14 val15
g h i val16 val17 val18

And there are many many more other rows.

As a result I want to have a table:

Table (col1, col2, col3, col4_1, col4_2, col4_3, col5_1, col5_2, col5_3, col6_1, col6_2, col6_3):

    a b c val1  val2  val3  null  null  null  null  null  null  
    d e f val4  val5  val6  val7  val8  val9  null  null  null      
    g h i val10 val11 val12 val13 val14 val15 val16 val17 val18

In words: I have some part of the table which has the same col1, col2, col3 but different col4, col5, col6. Number of rows per the same col1, col2, col3 varies from 1-3 (it is unknown beforehand).

My idea was to use GROUP BY, but how to handle col4, col5, col6 values which can be met in dynamic amount of rows?

How to make that kind of table?

Gordon Linoff · Accepted Answer · 2018-10-11 13:06:27Z

1

You can use conditional aggregation and row_number():

select col1, col2, col3,
       max(case when seqnum = 1 then col4 end) as col4_1,
       max(case when seqnum = 1 then col5 end) as col5_1,
       max(case when seqnum = 1 then col6 end) as col6_1,
       max(case when seqnum = 2 then col4 end) as col4_2,
       max(case when seqnum = 2 then col5 end) as col5_2,
       max(case when seqnum = 2 then col6 end) as col6_2,
       max(case when seqnum = 3 then col4 end) as col4_3,
       max(case when seqnum = 3 then col5 end) as col5_3,
       max(case when seqnum = 3 then col6 end) as col6_3
from (select t.*, row_number() over (partition by col1, col2, col3 order by col1) as seqnum
      from t
     ) t
group by col1, col2, col3

answered Oct 11, 2018 at 13:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Merge rows in postgresql with dynamic number of rows fitting the merge condition

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