请教一条汇总的SQL该如何写

Question

有一张表A记录了每个人负责的项目状态，字段为
Lead_Name,  Project_Name, Project_StatusProject_Status字段中分别含有的状态是Pending Ongoing Open想写一条SQL 可以生成一张表，将每个Lead所负责的Project的状态的数量列出来
例如：
Lead_Name   Ongoing  Open  Pending
Zhang          8       3     2   
Wang           4       2     3
Sun            12      4      7
现在只能想到这么写
SELECT lead_name, count([project_status])as Ongoing FROM A where Project_Status='Ongoing' group by project_lead要怎么改动一下才能实现我的要求，请大家指教一下啊 多谢 多谢SQL 汇总

Answer 1

select Lead_Name,Pending=SUM(case when Project_Status='Pending' then 1 else 0 end)
,Ongoing=SUM(case when Project_Status='Ongoing' then 1 else 0 end)
,[Open]=SUM(case when Project_Status='Open' then 1 else 0 end)
from tb
group by Lead_Name

Answer 2

谢谢 思路明白了
但是我才发现在Access里 这段SQL没有用，access里不支持case when
忘记一开始说这段SQL是要在Access里用的

Answer 3

搞定了 Access 里用switch代替select sim_project_lead,SUM(Switch(Project_Status='Pending',1,Project_Status='Open',0,Project_Status='Ongoing',0)) as Pending
    ,SUM(Switch(Project_Status='Pending',0,Project_Status='Open',1,Project_Status='Ongoing',0)) as [Open]
    ,SUM(Switch(Project_Status='Pending',0,Project_Status='Open',0,Project_Status='Ongoing',1)) as Ongoing
from TB where not (sim_project_lead ='N/A')
group by sim_project_lead