CODEWITHSUNDEEP
sqlamazon-redshift
Nilly
Nilly

Reputation: 103

How to merge rows in redshift

I have a table in redshift with a column with a common identifier, and a number of other attribute columns. However the common identifier appears in multiple rows, with a row for each attribute. I would like to merge them.

i.e.

Common ID | Attribute 1 | Attribute 2
123          X             null
123          null          Y
987          null          A
987          B             null

Would like to turn that into

Common ID | Attribute 1 | Attribute 2
123         X             Y
987         B             A

This essentially started out as me pivoting a table (using case statements) but the final output needs to merge or group by the common id (but there's no aggregation).

Note in the final product there are ~20 or so attributes, so a solution that is agnostic to the number of attributes is ideal. This also means that the common id could appear up to 20 or so times.

I've looked into listagg but want the final output to have the same number of columns, not one aggregated / concatenated column.

Upvotes: 4

Views: 1357

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use aggregation:

select common_id, max(attribute_1) as attribute_1, max(attribute_2) as attribute_2
from t
group by common_id;

You can probably fix this in the query that generates the data by fixing the group by keys.

Upvotes: 2

Related Questions