Skip to content
Advertisement

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.

Advertisement

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;
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement