Here is a version with the PIVOT operator in SQL Server 2005:

SELECT [name],
SUM([5]) AS note5,
SUM([4]) AS note4
FROM Foo
PIVOT (COUNT(note) FOR note IN ([5], [4])) AS P
GROUP BY [name];

HTH,

Plamen Ratchev
http://www.SQLStudio.com