Declare @numRecs int
set	@numRecs = 50

select	TOP (@numRecs) WITH TIES rank() over(order by sum(points) desc) rank, first + ' ' + last player, pos, sum(points) pts,
	dbo.ptsByTeam(id) team
from	players p
join	scores s
on	p.id = s.playerid
group by
	id, first, last, pos
order by
	[rank]

CREATE Function [dbo].[ptsByTeam] (
	@playerId int
)
Returns varchar(255)
AS
BEGIN

Declare @team varchar(32)
Declare @points int
Declare @str varchar(255)
Declare @tm varchar(32)
set	@str = ''

Declare c Cursor
read_only
for
	select	t.team, sum(points) pts
	from	scores s
	join	teams t
	on	t.id = s.team
	where	playerid = @playerId
	group by
		t.team
	order by	pts desc
Open c
fetch next from c into @team, @points
set @tm = @team

while @@fetch_status = 0
begin
	if @points <> 0
	begin
		set @str = @str + @team + ' (' + convert(varchar(5), @points) + '), '
	end
	fetch next from c into @team, @points
end

Close c
Deallocate c

If len(@str) > 1
begin
	set @str = left(@str, len(@str)-1)
end
If charindex(',', @str) = 0
begin
	return @tm
end

if @str is null
begin
	return @tm
end

Return ltrim(rtrim(@str))
END

SELECT Player,TG,
STUFF((SELECT ',' + TeamName FROM Teams WHERE TeamID = t.TeamID ORDER BY TeamID ASC FOR XML PATH('')),1,1,'') AS TeamList
FROM
(
SELECT Scorers.Player,COUNT(Scorers.Player) AS TG
FROM Scorers 
GROUP BY Scorers.Player
)t