Here is my two cents.

You don't need a Case Statement. Just simple One Line Query would work fine.

declare @MyTable as table

(

Column1 int,

Column2 int,

Column3 int,

column4 int

);

insert @MyTable values (0,6,0,0);

insert @MyTable values (0,0,30,0);

insert @MyTable values (4,0,0,0);

insert @MyTable values (0,0,0,18);

Select Col1 = Max(Column1), Col2 = Max(Column2), Col3 = Max(Column3), Col4 = Max(Column4) FROM @MyTable