Selecting max value between columns and replacing non min values to 0-CodePudding

I have 4 columns in table that each have different category values. I would like to find the maximum value between the columns and keep only that while turning all other values to 0. How can I go about doing this.

Reproducible example

CREATE TABLE #df (
    cat1 int,
    cat2 int,
    cat3 int,
    cat4 int
);


INSERT INTO #df
(
    cat1,
    cat2,
    cat3,
    cat4
)
VALUES
(   1, 0, 3, 4 ),
(   0, 2, 0, 4 ),
(   1, 2, 0, 0 ),
(   0, 0, 0, 4 )

SELECT * FROM #df

Final Table:

Cat1	Cat2	Cat3	Cat4
0	0	0	4
0	0	0	4
0	2	0	0
0	0	0	4

My attempt: This is close to what I want but instead of keeping the old columns, it creates a new column with the max value. I would like the same 4 columns as before but the non max values replaced to 0.

SELECT Cat1, Cat2, Cat3, Cat4, 
    (SELECT Max(Col) FROM (VALUES (Cat1), (Cat2), (Cat3), (Cat4)) AS X(Col)) AS TheMax
FROM #df

CodePudding user response：

Just use your existing statement as part of an apply then you can use an inline if (or case expression) to pick the required value:

select
  Iif(cat1 = themax, cat1, 0) cat1,
  Iif(cat2 = themax, cat2, 0) cat2,
  Iif(cat3 = themax, cat3, 0) cat3,
  Iif(cat4 = themax, cat4, 0) cat4
from t
cross apply (
  select Max(Col) from (values(Cat1), (Cat2), (Cat3), (Cat4))x(Col)
)m(themax)