CODEWITHSUNDEEP
sqlpostgresqlpivot
SimonRH
SimonRH

Reputation: 1469

How to move repeated column data into rows

How do I rewrite the data from col3 from the table below so:

col1    col2    col3    
1       1       5    
1       2       3    
1       2       4    
1       2       4    
1       2       6    
1       1       5    
1       2       7    
1       3       7

becomes

col1     col2   col3    col4    col5    col6    col7    colN
1       1       5       5
1       2       3       4       4       6       7
1       3       7

Upvotes: 0

Views: 41

Answers (2)

GMB
GMB

Reputation: 222482

For a fixed maximum number of columns, you can use row_number() and conditional aggregation:

select
    col1,
    col2,
    max(col3) filter(where rn = 1) col3,
    max(col3) filter(where rn = 2) col4,
    max(col3) filter(where rn = 3) col5,
    max(col3) filter(where rn = 4) col6,
    max(col3) filter(where rn = 5) col7
from (
    select
        col1,
        col2,
        row_number() over(partition by col1, col2 order by col3) rn
    from mytable
) t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would recommend that you put the results into an array rather than in separate columns. In order to put the data in separate columns, you will need some type of dynamic SQL.

So, perhaps this meets your needs:

select col1, col2, array_agg(col3 order by col3)
from t
group by col1, col2;

Upvotes: 1

Related Questions