I want to be able to display numbers with decimal parts equal to 0 as whole numbers but the ones with decimal parts not equal to 0 as decimal numbers. I've tried this code but it didn't work:

 CASE 
      WHEN Amount % 1 = 0 THEN CAST (AMOUNT AS INT)
      ELSE Amount    
 END as [DesiredOutput]

The Amount column is DECIMAL type

CodePudding user response：

Here is one solution

select *,
   case when
    amount = floor(amount) then FORMAT(amount, 'N0')
    else
    FORMAT(amount, 'N2')
    end as roundedPrice
from MyTable