Group rows in SQL by ID and Update Columns

Question

I have a table with columns ID,A1, C1,C2...C20, Country, State, Name and 10 other columns similar to country, state, name (A1 is my primary key)

My existing table looks like this

ID A1 C1 C2 C3 C4 Country State      Pet Name
1  25 1  1  0   1 USA     Texas      Tucker
1  26 1  0  0   0 USA     California Drum 
2  27 0  1  1   0 Canada  BC         Golden
2  28 0  0  0   1 USA     Ohio       Charlie
3  29 1  1  0   0 Mexico  Tabasco    Chelsea

How do I achieve this

ID A1 C1 C2 C3 C4 Country State      Pet Name
1  25 1  1  0   1 USA     Texas      Tucker
2  27 0  1  1   1 Canada  BC         Golden
3  29 1  1  0   0 Mexico  Tabasco    Chelsea

I want to group by Id and if there was 1 recorded any time I want it to be updated as 1 and I do not want the row which had the repeating information for same ID with a different A1. If there is an ID like 3 which has only one A1, I do not want it to have any changes. Once I get this, Id can become my primary key but I hope I can retain information for other columns such as country, state, name corresponding to A1 recorded first time(as shown in output table).

Please let me know how I can get this done, thanks

Answer 1

I think you want aggregation:

select id, min(a1) a1, max(c1) c1, max(c2) c2, max(c3) c3, max(c4) c4
from mytable
group by id

If you want to handle more columns, then it is different. I would recommend window max()s and row_number():

select *
from (select id, a1,
        max(c1) over(partition by id) c1,
        max(c2) over(partition by id) c2,
        max(c3) over(partition by id) c3,
        max(c4) over(partition by id) c4,
        country,
        state,
        row_number() over(partition by id order by a1) rn
    from mytable
) t
where rn = 1