CODEWITHSUNDEEP
sqlpostgresql
techkuz
techkuz

Reputation: 3981

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

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?

Upvotes: 0

Views: 151

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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

Upvotes: 1

Related Questions