Help with a pivot query

I have a view setup on SQL Server, which outputs a records in such way

id   time       winnerId
-------------------------------
35  1313114959  1
36  1313116182  1
37  1313116397  1
38  1313116596  2
39  1313116807  1
40  1313116858  1
41  1313116908  1
42  1313117708  2
46  1313118441  1
47  1313118719  1
48  1313120938  1
49  1313123767  1
50  1313159321  3

Now, when I am quering another table tbl_players, I want to take his id and count how many games he has won and display on his right. tbl_player

id    name
-----------
1    jack
2    hugh
3    man    

I want the results, in such way

id    name    win
-----------------
1    jack    9
2    hugh    2
3    man     1   

The total wins have to be counted, with the reference to the view, lets call it view_winner

How 开发者_如何学Cto write such a query?

you join the two tables together, and count up the number of times a winner id exists for each player

select p.id, p.name, count(w.winnerid)
from tbl_player p inner join someview w on w.winnerid = p.id
group by p.id, p.name, w.winnerid

You could join the tables together, and group by to count the number of wins:

select  p.id
,       p.name
,       count(w.winnerId)
from    tbl_players p
left join
        view_winner w
on      p.id = w.winnerId
group by
        p.id
,       p.name

You dont need a pivot for that output;

select 
    p.id as id,
    p.name as name,
    count(*) as win
from view_winner
    inner join tbl_player p on (p.id = winnerId)
group by p.id, name
order by p.id