CODEWITHSUNDEEP
sqlsql-server
Ghazal
Ghazal

Reputation: 101

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.

Upvotes: 1

Views: 43

Answers (3)

Andrea
Andrea

Reputation: 12405

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:

enter image description here

Upvotes: 0

Mureinik
Mureinik

Reputation: 312259

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:

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use aggregation:

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

Upvotes: 1

Related Questions