declare @sql varchar(8000)
set @sql = 'select 姓名'
select @sql = @sql + ',sum(case 科目 when '''+科目+''' then 成绩 else 0 end) ['+科目+']'
from (select distinct 科目 from 你的表) a
select xuehao,Xingming
,sum(case when kemu='数学' then fenshu else 0 end) shuxue
,sum(case when kemu='语文' then fenshu else 0 end) yuwen
,sum(case when kemu='英语' then fenshu else 0 end) yingyu
....
from table group by xuehao,Xingming
declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as a
declare @sql varchar(8000)
set @sql = 'select 部门'
select @sql = @sql + ',sum(case 学历 when '''+学历+''' then 1 else 0 end) ['+学历+'的人数]'
from (select distinct 学历 from 你的表) a
select xuehao,Xingming
,sum(case when kemu='数学' then fenshu else 0 end) shuxue
,sum(case when kemu='语文' then fenshu else 0 end) yuwen
,sum(case when kemu='英语' then fenshu else 0 end) yingyu
....
from table group by xuehao,Xingming