• 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 🙂