mssql display 1 decimal point if available, else show none

Question

Simple one I think.

I have a query that shows lengths of items.

Query:

select length from vw_OutstandingVsInStock1 OVI
LEFT JOIN Departments DEP
on OVI.Department COLLATE DATABASE_DEFAULT=DEP.Description
where OutstandingVolume>0.39 

This returns results like:

0.9
1.2
1.5
1.8
2.1
2.4
2.7
3.0
3.3
3.6...

In the case of 3.0 I want it to display as 3

so if no decimal value, show int with no decimals. if a decimal exists show decimal to 1 decimal point?

so desired output is 3 and 6 instead of 3.0 and 6.0

I am using MSSQL 2012.

Answer 1

Another variant (Fiddle)

;WITH vw_OutstandingVsInStock1 (length)
     AS (SELECT 0.9 UNION ALL
         SELECT 1.2 UNION ALL
         SELECT 1.5 UNION ALL
         SELECT 1.8 UNION ALL
         SELECT 2.1 UNION ALL
         SELECT 2.4 UNION ALL
         SELECT 2.7 UNION ALL
         SELECT 3.0 UNION ALL
         SELECT 3.3 UNION ALL
         SELECT 3.6)
SELECT IIF(length % 1 = 0,
           CAST(length AS INT),
           CAST(length AS SQL_VARIANT))
FROM   vw_OutstandingVsInStock1