CODEWITHSUNDEEP
sqlpivotamazon-redshift
GBA111
GBA111

Reputation: 1

Combining rows with same ID into single row with more columns

I would like to be able to combine rows with the same Common ID into the same row, adding cols as needed. Is there a way to do this easily in redshift? The answers I've found online mostly combine the rows into a single column.

Common ID | Attribute 1 | Attribute 2
a           A             B
b           C             D
a           E             F
c           G             H

Would like to turn that into

Common ID | Attribute 1 | Attribute 2 | Attribute 1b | Attribute 2b
a           A             B             E             F
b           C             D
c           G             H

Upvotes: 0

Views: 730

Answers (1)

GMB
GMB

Reputation: 222462

If you know in advance the maximum number of columns, you can use window functions and conditional aggregation:

select common_id,
    max(case when rn = 1 then attribute1 end) as attribute1_1,
    max(case when rn = 1 then attribute2 end) as attribute2_1,
    max(case when rn = 2 then attribute1 end) as attribute1_2,
    max(case when rn = 2 then attribute2 end) as attribute2_2
from (
    select t.*,
        row_number() over(partition by common_id order by attribute1, attribute2) rn
    from mytable t
) t
group by common_id

Upvotes: 1

Related Questions