求一句查询sql
现在有一张表
department projecttype projectstate time
13 2 1
9 1 2
9 1 2
9 3 1
9 1 3
13 2 4

想得到的结果（其实就是以的department 和projecttype进行分组，统计各个projectstate 状态的数量，还可能以time作为查询条件）
department projecttype 总数 projectstate1 projectstate2 projectstate3 projectstateother（不是1,2,3状态）
9 1 3 0 2 1 0
9 3 1 1 0 0 0
13 2 2 1 0 0 1

自己愚笨，感觉写出来的sql语句效率很低很低，求高人指定有没有高效的sql语句（sql server2008）

SQL code

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) aleft join (select department,projecttype,count(*) as numb from jiraproject(nolock) where projectstate=1 group by department,projecttype) bon a.department=b.department and a.projecttype=b.projecttypeleft 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.projecttypeleft join (select department,projecttype,count(*) as numd from jiraproject(nolock) where projectstate=3 group by department,projecttype) don a.department=d.department and a.projecttype=d.projecttype

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([department] int,[projecttype] int,[projectstate] int)goinsert [test]select 13,2,1 union allselect 9,1,2 union allselect 9,1,2 union allselect 9,3,1 union allselect 9,1,3 union allselect 13,2,4godeclare @str varchar(2000)set @str=''select     @str=@str+',[State'+LTRIM([projectstate])+']=sum(case when [projectstate]='    +LTRIM([projectstate])+' then 1 else 0 end)'from     testgroup 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    Total9    1    0    2    1    0    39    3    1    0    0    0    113    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