Complex Top Help Needed - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Top Help Needed

Author

Topic

dineshkunder
Starting Member

9 Posts

Posted - 2005-08-02 : 14:53:31

Thanks in advance! I am trying to display a LeaderBoard for BaseBall stats, so I need the top 5 players in columns like hits, homeruns, average, etc. etc. seven stats basically. Display will be as shown below:

Average | Hits | HomeRuns | Runs |etc. etc.
----------------------------------------------------------
John .765 | Mike 44 | David 5 | Josh 44 |
Josh .444 | Moe 42 | Lenny 4 | Lenny 42 |
Mike .421 | David 38 | Josh 2 | Chris 33 |

I would like to get back just one recordset from my stored proc to be displayed on a ASP page instead of 7 recordsets which I display in my table as shown above. But how do I go about combining the individual recordsets shown below so each row has the separate player info as shown above??

SELECT top 5 player_firstname + ':' + cast(SUM(h) as varchar) as HITS, SUM(h) as h
FROM profile_players
left outer join (select h,player_id from stats_hitting, games
where stats_hitting.game_id = games.game_id)
as hitstats
on profile_players.player_id = hitstats.player_id
GROUP BY player_firstname
order by h desc, player_firstname

SELECT top 5 player_firstname + ':' + cast(SUM(HR) as varchar) as HOMERUNS, SUM(hr) as hr
FROM profile_players
left outer join (select hr,player_id from stats_hitting, games
where stats_hitting.game_id = games.game_id)
as hitstats
on profile_players.player_id = hitstats.player_id
GROUP BY player_firstname
order by hr desc, player_firstname

SELECT top 5 player_firstname + ':' + cast(SUM(r) as varchar) as RUNS, SUM(r) as r
FROM profile_players
left outer join (select r,player_id from stats_hitting, games
where stats_hitting.game_id = games.game_id)
as hitstats
on profile_players.player_id = hitstats.player_id
GROUP BY player_firstname
order by r desc, player_firstname

etc. etc. for seven columns

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-08-03 : 01:58:34

2 options.
1.) Look up cross tabs on this site - That will resolve the problem for you in SQL.
2.) Union the recordsets together in a stored proc and use Reporting Services to produce the web page - using the matrix option - instead of a table.

Option 2 is probably the best method :)

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources