sql的统计查询问题！(100分)

本人有个表如下：
单位　　　分数
A　　　　90
A 80
B 70
C 60
B 50
B 70
C 90
要实现以下的统计表格：
单位　90　80　70　60　50
A　　　1　1　　0　0　　0
B　　　1　0　　2　0　　0
C　　　1　0　　0　1　　1
A+B　　2 1 2 0 0
合计（A+B+C）3　　1　　2　0　1
这样如何用SQL实现！

使用case函数进行处理

用临时表吧,Case你会晕的,何况部门和分数都是不定的吧:
declare @SQL varchar(2000)
declare @SQLSel varchar(200)
declare @SQLPart1 varchar(200)
declare @SQLPart2 varchar(200)
declare @Part varchar(20)
declare @Score int
if exists(select * from tempdb..sysobjects where name='##temp1')
drop table ##temp1
create table ##temp1 (AutoID int identity(1,1),Part Varchar(20))
Insert Into ##Temp1(Part) Select Distinct Part From Table1
Set @SQLPart1=''
Set @SQLSel=''
DECLARE abc CURSOR FOR select distinct Score FROM table1 order by Score Desc
OPEN abc
FETCH NEXT FROM abc INTO @Score
WHILE (@@FETCH_STATUS = 0) begin
Set @SQLSel=@SQLSel+',['+Cast(@Score As Varchar)+']'
Set @SQLPart1=@SQLPart1+',Sum(IsNull(['+Cast(@Score As Varchar)+'],0)) As ['+Cast(@Score As Varchar)+']'
Set @SQL='Alter Table ##temp1 Add ['+Cast(@Score As Varchar)+'] int not Null default(0)'
Exec(@SQL)
Set @SQL='Update ##Temp1 Set ['+Cast(@Score As Varchar)+']=IsNull(Z.iCount,0) From (Select Part,Count(*) As iCount From Table1 Where Score='+Cast(@Score As Varchar)+' Group By Part) Z Where ##Temp1.Part=Z.Part'
Exec(@SQL)
FETCH NEXT FROM abc INTO @Score
end
CLOSE abc
DEALLOCATE abc
Set @SQLPart2=''
DECLARE abc CURSOR FOR select distinct Part FROM table1 order by Part
OPEN abc
FETCH NEXT FROM abc INTO @Part
WHILE (@@FETCH_STATUS = 0) begin
if (@SQLPart2='')
Set @SQLPart2=@Part
else
begin
Set @SQL='Insert Into ##Temp1(Part'+@SQLSel+') Select '''+@SQLPart2+'+'+@Part+''''+@SQLPart1+' From ##Temp1 Where Part In ('''+Replace(@SQLPart2,'+',''',''')+''')'
print @SQL
Exec(@SQL)
Set @SQLPart2=@SQLPart2+'+'+@Part
end
FETCH NEXT FROM abc INTO @Part
end
select * from ##temp1

select a.单位,isnull(九十分,0) as '90',isnull(八十分,0) as '80',isnull(七十分,0) as '70',isnull(六十分,0) as '60',isnull(五十分,0) as '50' from
((((select 单位,count(*) as 九十分 from lx where 分数=90 group by 单位) a full join
(select 单位,count(*) as 八十分 from lx where 分数=80 group by 单位) b on a.单位=b.单位) full join
(select 单位,count(*) as 七十分 from lx where 分数=70 group by 单位) c on a.单位=c.单位) full join
(select 单位,count(*) as 六十分 from lx where 分数=60 group by 单位) d on a.单位=d.单位) full join
(select 单位,count(*) as 五十分 from lx where 分数=50 group by 单位) e on a.单位=e.单位
union
select a.单位,isnull(九十分,0) as '90',isnull(八十分,0) as '80',isnull(七十分,0) as '70',isnull(六十分,0) as '60',isnull(五十分,0) as '50' from
((((select 'A+B' as 单位,count(*) as 九十分 from lx where 分数=90 and (单位='A' or 单位='B') ) a full join
(select 'A+B' as 单位,count(*) as 八十分 from lx where 分数=80 and (单位='A' or 单位='B') ) b on a.单位=b.单位) full join
(select 'A+B' as 单位,count(*) as 七十分 from lx where 分数=70 and (单位='A' or 单位='B') ) c on a.单位=c.单位) full join
(select 'A+B' as 单位,count(*) as 六十分 from lx where 分数=60 and (单位='A' or 单位='B') ) d on a.单位=d.单位) full join
(select 'A+B' as 单位,count(*) as 五十分 from lx where 分数=50 and (单位='A' or 单位='B') ) e on a.单位=e.单位
union
select a.单位,isnull(九十分,0) as '90',isnull(八十分,0) as '80',isnull(七十分,0) as '70',isnull(六十分,0) as '60',isnull(五十分,0) as '50' from
((((select 'A+B+C' as 单位,count(*) as 九十分 from lx where 分数=90 ) a full join
(select 'A+B+C' as 单位,count(*) as 八十分 from lx where 分数=80 ) b on a.单位=b.单位) full join
(select 'A+B+C' as 单位,count(*) as 七十分 from lx where 分数=70 ) c on a.单位=c.单位) full join
(select 'A+B+C' as 单位,count(*) as 六十分 from lx where 分数=60 ) d on a.单位=d.单位) full join
(select 'A+B+C' as 单位,count(*) as 五十分 from lx where 分数=50 ) e on a.单位=e.单位
在查询分析器中测试正确，满足楼主的要求。lx 为楼主的表。

你可以試用左聯接統計。

用group不行么？
where语句再判断一下应该可以巴

谢谢！不过用游标是滞会影响速度；
liyoumin你的结果写死了，不能动了！

多人接受答案了。