Well, I have finally found the time to come back to this.
I have gone with a query such as
IIF() isn't in SQL 2008 R2 - it was only added in 2012.
SELECT Pupil.Last_Name + ' ' + Pupil.First_Name AS Name, Pupil.Last_Name, Pupil.First_Name, SUM(CASE Point_Category.ShortName WHEN 'HW-' THEN Point_Entry.Value ELSE 0 END) AS [HW-],
SUM(CASE Point_Category.ShortName WHEN 'LoE' THEN Point_Entry.Value ELSE 0 END) AS LoE,
SUM(CASE Point_Category.ShortName WHEN 'Present' THEN Point_Entry.Value ELSE 0 END) AS Present
INNER JOIN Point_Entry ON Point_Entry.Pupil_ID = Pupil.ID
INNER JOIN Point_Category ON Point_Category.ID = Point_Entry.Point_Category_ID
INNER JOIN Reg_Group ON Reg_Group.ID = Pupil.Reg_Group
WHERE (Reg_Group.Name LIKE '08HS')
GROUP BY Pupil.First_Name, Pupil.Last_Name
ORDER BY Pupil.Last_Name, Pupil.First_Name
But the above works for me.
The category names are filled out using PHP - ie. I grab all the column names in one query, then create the above using those categories.