Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Tim
Starting Member
392 Posts |
Posted - 2001-06-18 : 22:20:57
|
This (simplified) database stores results of tennis matches in 3 tables:tblMatch ID Date VenuetblMatchPlayer ID MatchID PlayerID ResulttblPlayer ID NameFor every match there are 2 records written to tblMatchPlayer 1 for each player involved.The result column stores 'W' for a win and 'L' for a loss.Requirement is to provide a summary of all matches played since a selected date, with one player per row and the following columns:- PlayerName- NumberMatchesPlayed- PercentWins- PercentLosses- Form'Form' is a string that shows the results of each player's games in the same period, in chronological order with most recent first, eg: 'WLLWL'I did make a single query that could do this but it was way to ineffiecient to run over a table with many thousands of rows.So I resorted to a cursor which does everything but the 'form' in a select with group by. For each row returned by the cursor I do a second query which calculates the form.Any cursor crushers out there can do it in single sql statement that is feasible with large nbr records?Edited by - tim on 06/18/2001 22:23:16 |
|
|
|
|