select bj ,sum(语文),sum(数学),sum(英语) from ( select bj,case kc where '语文' then pjf else 0 end as 语文,case kc where '数学' then pjf else 0 end as 数学,case kc where '英语' then pjf else 0 end as 英语
from table ) a
group by bj
from table ) a
group by bj
调试欢乐多
set @sql=''
set @sql='select bj'
select @sql=@sql+',sum(case kc when '''+kc+''' then pjf else '' end)['+kc+']
from (select distinct kc from 表)a
set @sql=@sql+' from 表 group by bj'
exec(@sql)
sum(case when kc='语文' then pjf else 0 end),
sum(case when kc='数学' then pjf else 0 end),
sum(case when kc='英语' then pjf else 0 end)
from 表
'991计', '语文', 75 union all select
'991计', '数学', 76 union all select
'991计', '英语', 73 union all select
'992计', '语文', 72 union all select
'992计', '英语', 70 union all select
'993计', '数学', 78select bj as 班级,
sum(case kc when '语文' then pjf else null end) as 语文,
sum(case kc when '数学' then pjf else null end) as 数学,
sum(case kc when '英语' then pjf else null end) as 英语
from tab1
group by bj----- ----------- ----------- -----------
991计 75 76 73
992计 72 NULL 70
993计 NULL 78 NULL(所影响的行数为 3 行)
declare @sql varchar(3000)
set @sql=''
set @sql='select bj'
select @sql=@sql+',sum(case kc when '''+kc+''' then pjf else '' end)['+kc+']'
from (select distinct kc from 表)a
set @sql=@sql+' from 表 group by bj'
exec(@sql)
create table tn(bj varchar(20),kc varchar(20),pjf int)
insert tn select
'991计', '语文', 75 union all select
'991计', '数学', 76 union all select
'991计', '英语', 73 union all select
'992计', '语文', 72 union all select
'992计', '英语', 70 union all select
'993计', '数学', 78--测试
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when kc='''+kc+''' then pjf else null end) as ['+kc+']'
from tn
group by kcexec('select bj'+@sql+' from tn group by bj')--结果
班级 数学 英语 语文
991计 76 73 75
992计 NULL 70 72
993计 78 NULL NULL