SQL Server Combine Duplicate Entries-CodePudding

I have a Microsoft SQL table with the following data:

CustomerID   Location1   Location2
788          A           NULL
788          A           B
788          B           NULL
649          A           NULL
649          NULL        B
936          B           NULL

I'd like to be able to query this table and return just the CustomerID and the combined unique locations.

CustomerID   Location1   Location2
788          A           B
649          A           B
936          B

Select Distinct CustomerID, MAX(Location1) L1, MAX(Location2) L2
FROM table
GROUP BY CustomerID

returns

CustomerID   L1   L2
788          B    B
649          B
936          B

Having a difficult time working this one out. Any assistance would be greatly appreciated.

CodePudding user response：

use min and max:

Select CustomerID
       , MIN(coalesce(Location1,Location2)) L1
       , MAX(coalesce(Location2,Location1)) L2
FROM table
GROUP BY CustomerID

CodePudding user response：

This should solve OP's last comments:

with u as
(select CustomerID, Location1 as L
from Table1
where Location1 is not null
union select CustomerID, Location2 as L 
from Table1
where Location2 is not null)
(select customerID, min(L) as Location1, case when max(L) <> min(L) then max(L) end as Location2
from u
group by CustomerID)

Fiddle