Format sql column using select

Question

I have the following query to select a record, but I want to format a column in the result set.

SELECT 
    COALESCE(dbo.tblMitchellLandscapeID.PatchSize,0) as PatchSize,
    dbo.tblMitchellLandscape.MitchellLandscapeName
FROM tblMitchellLandscapeID
INNER JOIN dbo.tblMitchellLandscape
      ON dbo.tblMitchellLandscapeID.MitchellLandscapeID=dbo.tblMitchellLandscape.MitchellLandscapeID
WHERE AssessmentVersionID = @AssessmentVersionID

"PatchSize" is a decimal value, so it is always stored as two decimal places "15.10". All that I try to format to one decimal when executing the select statement, I want to populate a result set, for example, "15.1", and not 15.10.

Answer 1

You can simply translate it into the desired format:

SELECT CAST(COALESCE(li.PatchSize, 0) as decimal(5, 1)) as PatchSize,
       l.MitchellLandscapeName
FROM tblMitchellLandscapeID li INNER JOIN
     dbo.tblMitchellLandscape l
     ON li.MitchellLandscapeID = l.MitchellLandscapeID
WHERE AssessmentVersionID = @AssessmentVersionID;

Note that a query is also easier to read (and write) if you use table aliases.