How do I apply coalesce to rows to eliminate the nulls?

I am trying to transform this:

Email	ADName	ADDName
[email protected]	NULL	Dave Davis
[email protected]	Dave Davis	NULL
[email protected]	NULL	NULL
[email protected]	NULL	NULL

Into this:

Email	ADName	ADDName
[email protected]	Dave Davis	Dave Davis

So I thought the SQL COALESCE (Microsoft SQL Server 2012) would do the trick, but I am not sure it's the tool for the job. I get the impression it's not intended to operate across rows.

Thought I'd try a simple start on one col:

SELECT COALESCE (       
        SELECT 
          ADName
        FROM 
            MyTable
)

But that doesn't even pass the syntax check. What is the correct way of doing it?

EDIT: OK, so doing a Group By, Max solves the immediate problem on a column where there are only two possible values in the group.

What about if it is more complex, with more columns and more values to evaluate? As in

Email	ADName	ADDName	OtherName
[email protected]	NULL	Dave Davis	NULL
[email protected]	Dave Davis	NULL	NULL
[email protected]	NULL	NULL	Dave Davis (User)
[email protected]	NULL	NULL	Dave Davis (Admin)

I am seeking this final result:

Email	ADName	ADDName	OtherName
[email protected]	Dave Davis	Dave Davis	Dave Davis (User)
[email protected]	Dave Davis	Dave Davis	Dave Davis (Admin)

I have the suspicion this is becoming a python problem or similar.

Solution

I think that to get all combinations you would want to do something like this:

select E.email, X.ADName, Y.ADDName, Z.otherName
from (select T.email from T group by T.email) as E
left join (select T.email, T.ADName from T where T.ADName is not null) X on X.email = E.email
left join (Select T.email, T.ADDName where T.ADDNAME is not null) Y on Y.email = E.email
left join (select T.email, T.otherName from T where T.otherName is not null) Z on Z.email = E.email