[FIXED] How to display numbers with decimal part equal to 0 as a whole number in T-SQL?

Issue

Column1	Amount	DesiredOutput
Item1	85.00	85
Item2	52.25	52.25
Item3	36.85	36.85
Item4	67.00	67

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

Solution

Here is one solution

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

Answered By - Thomas Koelle

Answer Checked By - David Marino (PHPFixing Volunteer)