Thanks for the tip Chris, I have never come across the CASE command before.
was easy in the end 🙂
select r.r_age_group, r.r_event, p.firstname + ' ' + p.surname,
sum(case when r.r_event_id=1 then r.r_points else 0 END) as 'Event1',
sum(case when r.r_event_id=2 then r.r_points else 0 END) as 'Event2',
sum(r.r_points) as 'Total'
from results r, player p
where r.r_player_number = p.player_number
group by r.r_age_group, r.r_event, p.firstname + ' ' + p.surname
order by r.r_age_group, r.r_event, total desc
Thanks again 🙂