select 年级,(select 人数 from tablename a where a.年级=b.年级 and a.课目='数学') as 数学 ,(select 人数 from tablename a where a.年级=b.年级 and a.课目='语文') as 语文 from tablename b group by 年级 order by 年级
if object_id('test') is not null drop table test
select '2001' as 年级, '语文' as 课目, 20 as 人数
into test
union select '2001', '数学', 15
union select '2002', '语文', 20
union select '2002', '数学', 15
union select '2003', '语文', 20
union select '2003', '数学', 15
select * from test
declare @s varchar(1000)
set @s = 'select 年级'
select @s = @s + ', sum(case 课目 when ''' + 课目 + ''' then 人数 end) as ' + 课目
from (select distinct 课目 from test) a
set @s = @s + ' from test group by 年级'
exec(@s)