Coalesce Columns in SQL Server

I have two rows in a table like below:

jack johnson NULL Male
jack johnson 27 NULL


and I want to be able to combine them into one row:

jack johnson 27 Male


I have tried coalescing the two columns but what I end up getting is:

jack johnson male
jack johnson 27


I'm not sure how to proceed. Any help or tips would be appreciated.

3 Answers
3

You can use aggregation:

select col1, col2, max(col3) as col3, max(col4) as col4
from t
group by col1, col2;


If you know that only one row will have a value for each column, you can take advantage of the fact that max (and min) ignore nulls. If there's just one value in the column, max will just return it:

max

min

null

max

SELECT firstname, lastname, MAX(age), MAX(gender)
FROM mytable
GROUP BY firstname, lastname


Use group by with max:

declare @tmp table ([first_name] nvarchar(50),[last_name] nvarchar(50), age int, sex nvarchar(50))
insert into @tmp values
('jack','johnson',NULL, 'Male')
,('jack','johnson',27 , NULL)

select first_name, last_name, max(age) as age, max (sex) as sex from @tmp
group by first_name, last_name


Result:

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.