sql

create table A
(
stuName varchar(10),
stuClass varchar(10),
stuScroe float,
)

insert into A (stuName,stuClass,stuScroe)values('张三','数学',80)
insert into A (stuName,stuClass,stuScroe)values('张三','语文',70)
insert into A (stuName,stuClass,stuScroe)values('张三','英语',60)
insert into A (stuName,stuClass,stuScroe)values('李四','数学',90)
insert into A (stuName,stuClass,stuScroe)values('李四','语文',80)
insert into A (stuName,stuClass,stuScroe)values('王五','数学',95)
insert into A (stuName,stuClass,stuScroe)values('王五','英语',85)

--假设姓名[stuName]是唯一的，就是说相同名字的就是同一个人
--学科[stuClass]也是唯一的，就是说相同学科的就是同一个学科

select stuName,stuClass,sum(stuScroe) from A group by stuClass,stuName

declare @str varchar(400)
set @str=''

select @str=@str+','+stuClass+'=max(case when stuClass='''+stuClass+''' then stuScroe else 0 end)'
from (select distinct stuClass from A)t

exec('select stuName ' +@str+' from A group by stuName ')


declare @str varchar(400)
set @str=''

select @str=@str+','+stuClass+'=max(case when stuClass='''+stuClass+''' then stuScroe else 0 end)'from (select distinct stuClass from A)t
set @str='select isnull(stuName,''合计'') ' +@str+',总分=sum(stuScroe) from A group by stuName with rollup'

exec(@str)


@str=select isnull(stuName,'合计') ,数学=max(case when stuClass='数学' then stuScroe else 0 end),英语=max(case when stuClass='英语' then stuScroe else 0 end),语文=max(case when stuClass='语文' then stuScroe else 0 end),总分=sum(stuScroe) from A group by stuName with rollup