Set two decimal places after AVG function inside PIVOT

SELECT 
   Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
(
   SELECT  
       Grades, Subject, Student
   FROM    
       Grade_Report
) AS sourcetable
PIVOT
(
   AVG(Grades)
   FOR [Subject] IN ([English], [Mathematics], [Science], [Programming], [History])
) AS pivoted

The output keeps being 95.000000

I tried using AVG( CAST(Grades AS Decimal(10,2) ) ) inside the pivot, and it keeps returning a syntax error about (.

Tried using CAST AS DECIMAL inside the FROM subquery, but it just keeps putting the same 00000 output. CAST AS FLOAT works but I need two decimal places and it eliminates trailing 0s

Using ROUND(AVG) inside the pivot ends up being unrecognized aggregate.

Answer

You want two decimal positions for all your numbers, even if these digits are not significant. This reads like a pure formatting issue.

I would recommend format():

SELECT Student, 
    format([English],     '0.00') as [English], 
    format([Mathematics], '0.00') as [Mathematics],
    format([Science],     '0.00') as [Science],
    format([Programming], '0.00') as [Programming],
    format([History],     '0.00') as [History]
FROM ...

Note that this turns the numbers to strings – which seems to be what you are actually asking here.