select study,
A=sum(case score when 'A' then 1 else 0 end),
B=sum(case score when 'B' then 1 else 0 end),
C=sum(case score when 'C' then 1 else 0 end),
D=sum(case score when 'D' then 1 else 0 end)
from 表 group by study
A=sum(case score when 'A' then 1 else 0 end),
B=sum(case score when 'B' then 1 else 0 end),
C=sum(case score when 'C' then 1 else 0 end),
D=sum(case score when 'D' then 1 else 0 end)
from 表 group by study
Study,
[A]=sum(case when score='A' then 1 else 0 end),
[B]=sum(case when score='B' then 1 else 0 end),
[C]=sum(case when score='C' then 1 else 0 end),
[D]=sum(case when score='D' then 1 else 0 end)
.....
from 表
Group by Study
set @s='select study'
select @s=@s+',['+score+']=sum(case score when '''+score+''' then 1 else 0 end)'
from 表 group by score order by score
set @s=@s+' from 表 group by study'
exec(@s)
set @s = ''
select @s = @s +','+score+'= sum(case score when '+scroe+' then 1 else 0 end'
from (select distinct score from table) a
exec('select study '+@s+' from table group by study')
set @s = ''
select @s = @s +','+score+'= sum(case score when '''+scroe+''' then 1 else 0 end'
from (select distinct score from table) a
exec('select study '+@s+' from table group by study')
cast(sum(case score when 'B' then 1 end) as varchar(4)) + '人' as B,
cast(sum(case score when 'C' then 1 end) as varchar(4)) + '人' as C,
cast(sum(case score when 'D' then 1 end) as varchar(4)) + '人' as D
from tablename
group by study
insert tb
select 1,'数学','张','A' union all
select 2,'数学','王','B' union all
select 3,'数学','刘','C' union all
select 4,'数学','李','A' union all
select 5,'语文','李','A' union all
select 6,'语文','刘','C' union all
select 7,'语文','张','A'go
declare @s varchar(8000)
set @s = ''
select @s = @s +',['+score+']= convert(varchar(3),sum(case score when '''+score+''' then 1 else 0 end))'
from (select distinct score from tb) aexec('select study '+@s+' from tb group by study')drop table tb/*
study A B C
-------- ---- ---- ----
数学 2 1 1
语文 2 0 1
*/
insert t
select 1,'数学','张','A' union all
select 2,'数学','王','B' union all
select 3,'数学','刘','C' union all
select 4,'数学','李','A' union all
select 5,'语文','李','A' union all
select 6,'语文','刘','C' union all
select 7,'语文','张','A'
declare @sql varchar(8000),@i intselect @sql='select study',@i=65while @i<70
select @sql=@sql+',['+char(@i)+']=
cast(isnull(sum(case when score='''+char(@i)+''' then 1 end),0) as varchar)+
case when isnull(sum(case when score='''+char(@i)+''' then 1 end),0)>0
then ''个人'' else '''' end',@i=@i+1select @sql=@sql+' from t group by study'
exec(@sql) drop table tstudy A B C D E
-------------------- ---------------------------------- ---------------------------------- ---------------------------------- ---------------------------------- ----------------------------------
数学 2个人 1个人 1个人 0 0
语文 2个人 0 1个人 0 0警告: 聚合或其它 SET 操作消除了空值。
insert tb
select 1,'数学','张','A' union all
select 2,'数学','王','B' union all
select 3,'数学','刘','C' union all
select 4,'数学','李','A' union all
select 5,'语文','李','A' union all
select 6,'语文','刘','C' union all
select 7,'语文','张','A'go
declare @s varchar(8000)
set @s = ''
select @s = @s +',['+score+']= convert(varchar(3),sum(case score when '''
+score+''' then 1 else 0 end))'+'+''(人)'''
from (select distinct score from tb) aexec('select study '+@s+' from tb group by study')drop table tb/*
study A B C
-------- ------- ------- -------
数学 2(人) 1(人) 1(人)
语文 2(人) 0(人) 1(人)
*/