View with dynamic columns help

Hi. I was wondering if anyone could help me with this view.

I have 2 tables.

TableA (Holds Event Scores)
--------------------------------------------
Player EventID Score
Jim | 1 | 3
Jim | 2 | 2
Jim | 2 | 4
Dave | 1 | 2
Dave | 2 | 2
Dave | 2 | 3
Dave | 3 | 1
Dave | 3 | 3

TableB (Holds Event Names)
---------------------------------------------
Event EventID
Running | 1
Jumping | 2
Diving | 3

I wanted to join the two tables but only have 1 row for each player. The table would have a column for each Event in TableB with the players name and the event with the sum. So the end result would look like this:

View
--------------------------------------
Player Running Jumping Diving
Jim | 3 | 6 | 0
Dave | 2 | 5 | 4

Is it possible to create column names dynamically based off of a tables column value? If so, could anyone help me with this view?

 

You are going to run into problems because your first table has no primary key. This would also be easier if there were a third table. The third table would be playerID, player. Your tableA should look like ScoreID, playerID, eventID, score. Without the scoreID, you could run into problems with matching rows. If the score in Jim's third entry were 2, then that row would match the second entry and could confuse the db.

You can do what you want by using nested select statements. For example (note- my example assumes three tables):

tblPlayer
-->intPlayerID (int) pk
-->chrPlayer (char)

tblEvents
-->intEventID (int) pk
-->chrEvent (char)

tblScores
-->intScoreID (int) pk
-->intPlayerID (int) fk
-->intEventID (int) fk
-->intScore (int)

select chrPlayer as 'Player', (select sum(intScore) from tblScores r where intEventID = 1 and r.intPlayerID = p.intPlayerID) as 'Running', (select sum(intScore) from tblScores j where intEventID = 2 and j.intPlayerID = p.intPlayerID) as 'Jumping', (select sum(intScore) from tblScores d where intEventID = 3 and d.intPlayerID = p.intPlayerID) as 'Diving'
from tblPlayer p


Or something like that....