How to combine multiple records in one in SQL

Question

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.

Tag your question with the database you are using. – Gordon Linoff Jun 07 '21 at 14:51 — Gordon Linoff, Jun 07 '21 at 14:51

score 1 · Accepted Answer · answered Jun 07 '21 at 14:53

1

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;

answered Jun 07 '21 at 14:53

Gordon Linoff

1,242,037
58
646
786

Coalesce returns first not-null value? And I have phone number values that are both not-null... – robotic_arm13 Jun 07 '21 at 15:59
@robotic_arm13 . . . I don't understand your comment. You understand `coalesce()`. The first argument is conditional. – Gordon Linoff Jun 07 '21 at 16:00

score 0 · Answer 2 · answered Jun 07 '21 at 15:08

0

You could use the JOIN keyword to join the two tables together with the max values included.

Here's a link to reference the code you're looking for: Select only rows by join tables max value

answered Jun 07 '21 at 15:08

Jamil Matheny

180
1
7

score 0 · Answer 3 · answered Jun 07 '21 at 15:09

You can use an autoreferenced JOIN to search the not null rows and OP = I, to not depends of a MAX

SELECT a.OP, a.ID,
COALESCE(a.'First name',b.'First name'),
COALESCE(a.'Last name',b.'Last name'),
'Phone number'
FROM table a
LEFT JOIN (SELECT * FROM table WHERE OP = 'I' AND and 'First name' IS NOT NULL AND 'Last name' IS NOT NULL LIMIT 1) b ON b.ID = a.ID;

How to combine multiple records in one in SQL

3 Answers3