求一SQL 语句

[Ricky001]

有一表：table1
表内容如下：(Type 为型号，Grade 为等级(字段值为A，B,C,D四种等级)
Type Grade
a A
b A
a B
b B
c D
b B
a A
f C
... ...

现要求统计得出另外一个表：(AmtA 为等级A的合计总数，AmtB为等级B的合计总数，AmtC为等级C的合计总数，AmtD为等级D的合计总数）
要求格式：
type AmtA AmtB AmtC AmtD
a 2 1
b 1 2
c 1
f 1

[Ciny_Cou]

create table #t(Type char(1), Grade char(1))
insert into #t select 'a','A'
union all
select 'b','A'
union all
select 'a','B'
union all
select 'b','B'
union all
select 'c','D'
union all
select 'b','B'
union all
select 'a','A'
union all
select 'f','C'
select * from #t
select distinct a.Type,AmtA=(select Count(Grade) from #t as b where b.Type=a.Type and b.Grade='A')
,AmtB=(select Count(Grade) from #t as b where b.Type=a.Type and b.Grade='B')
,AmtC=(select Count(Grade) from #t as b where b.Type=a.Type and b.Grade='C')
,AmtD=(select Count(Grade) from #t as b where b.Type=a.Type and b.Grade='D')
from #t as a
drop table #t

--结果
-------------------------------------
Type Grade
---- -----
a A
b A
a B
b B
c D
b B
a A
f C

（所影响的行数为 8 行）

Type AmtA AmtB AmtC AmtD
---- ----------- ----------- ----------- -----------
a 2 1 0 0
b 1 2 0 0
c 0 0 0 1
f 0 0 1 0

（所影响的行数为 4 行）

[brooks105]

create table a
(Type char(5), Grade char(5))
insert a
select 'a' ,'A'
union all
select 'b' ,'A'
union all
select 'a' ,'B'
union all
select 'b' ,'B'
union all
select 'c' ,'D'
union all
select 'b' ,'B'
union all
select 'a' ,'A'
union all
select 'f' ,'C'

select * from a


select type, AmtA=sum(case when Grade='A' then 1 else '' end)
, AmtB=sum(case when Grade='B' then 1 else '' end)
, AmtC=sum(case when Grade='C' then 1 else '' end)
, AmtD=sum(case when Grade='D' then 1 else '' end)
from a
group by type

type AmtA AmtB AmtC AmtD
----- ----------- ----------- ----------- -----------
a 2 1 0 0
b 1 2 0 0
c 0 0 0 1
f 0 0 1 0

（所影响的行数为 4 行）

[Ricky001]

谢谢两位，先试一下

[brooks105]

如果处理n种情况,建议用动态语句

declare @sql varchar(8000)
set @sql='select type'
select @sql=@sql+',sum(case Grade when '''+Grade+''' then 1 else 0 end)'+Grade
from (select distinct Grade from a) s
select @sql=@sql+'from a group by type'
exec(@sql)

type A B C D
----- ----------- ----------- ----------- -----------
a 2 1 0 0
b 1 2 0 0
c 0 0 0 1
f 0 0 1 0

[yuweiwei]

declare @sql varchar(1000)
set @sql='select type,'
select @sql=@sql+'sum(case when grade='''+tt.grade+''' then 1 else 0 end) as amt'+tt.grade+','
from (select grade from a11 group by grade)tt

set @sql=left(@sql,len(@sql)-1)+ 'from a11 group by type'
print @sql
exec (@sql)

[yuweiwei]

晕楼上的抢先了!!!