select a.department,a.projecttype,number,numb,numc,numd，(number-isnull(numb,0)-isnull(numc,0)-isnull(numd,0)) as othernum, from
(select department,projecttype,count(*) as number from jiraproject(nolock) group by department,projecttype) a
left join 
(select department,projecttype,count(*) as numb from jiraproject(nolock) where projectstate=1 group by department,projecttype) b
on a.department=b.department and a.projecttype=b.projecttype
left join 
(select department,projecttype,count(*) as numc from jiraproject(nolock) where projectstate=2 group by department,projecttype) c 
on a.department=c.department and a.projecttype=c.projecttype
left join 
(select department,projecttype,count(*) as numd from jiraproject(nolock) where projectstate=3 group by department,projecttype) d
on a.department=d.department and a.projecttype=d.projecttype

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[department] int,
[projecttype] int,
[projectstate] int
)
go
insert [test]
select 13,2,1 union all
select 9,1,2 union all
select 9,1,2 union all
select 9,3,1 union all
select 9,1,3 union all
select 13,2,4
go

declare @str varchar(2000)
set @str=''
select 
    @str=@str+',[State'+LTRIM([projectstate])+']=sum(case when [projectstate]='
    +LTRIM([projectstate])+' then 1 else 0 end)'
from 
    test
group by 
    [projectstate]
exec('select [department],[projecttype]'+
@str+',count(1) as Total from test group by [department],[projecttype] order by 1')
/*
department    projecttype    State1    State2    State3    State4    Total
9    1    0    2    1    0    3
9    3    1    0    0    0    1
13    2    1    0    0    1    2
*/

------解决方案--------------------

SQL code
select department,
       总数=count(*),
       projectstate1=sum(case when projectstate=1 then 1 else 0 end),
       projectstate2=sum(case when projectstate=2 then 2 else 0 end),
       projectstate3=sum(case when projectstate=3 then 3 else 0 end),
       projectstateother=sum(case when projectstate not in(1,2,3) then 1 else 0 end)
from jiraproject group by department

------解决方案--------------------
case when