What about:
SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
FROM Table1
GROUP BY UnitID
;
This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.
Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.