How to combine multiple records in one in SQL

I have a SQL table that looks something like this:

OP	ID	First name	Last name	Phone number
I	123	John	Smith	888-555
U	123			777-555

I have to combine this rows through select query into something like this:

ID	First name	Last name	Phone number
123	John	Smith	777-555

I have trouble writing query because my only idea is with MAX, but sometimes is field from U row lesser than the one in I row and I need to check every column.

What I tried:

Select ID,
max('First name') as 'First name',
max('Last name') as 'Last name',
max('Phone number') as 'Phone number'
from table
group by ID

Please help.

Answer

You seem to want a priority, with U first then I. One method is to use coalesce() with conditional aggregation:

select id,
       coalesce(max(case when OP = 'I' then first_name end),
                max(first_name)
               ) as first_name,
       coalesce(max(case when OP = 'I' then last_name end),
                max(last_name)
               ) as last_name,
       coalesce(max(case when OP = 'I' then phone_number end),
                max(phone_number)
               ) as phone_number
from t
group by id;