subject固定的前提下,可以用如下非动态SQL实现:
------------------------------------------------------------------
select
姓名 = name,
语文 = isnull(max(case subject when '语文' then grade end),0),
数学 = isnull(max(case subject when '数学' then grade end),0),
英语 = isnull(max(case subject when '英语' then grade end),0),
总分 = sum(grade)
from
表
group by
name
Create Table CC(id int,name varchar(10),subject varchar(10),grade int)
insert into CC
Select 1,'張三', '語文', 1 union all
Select 2,'李四', '數學', 2 union all
Select 3,'王五', '英語', 1 union all
Select 4,'張三', '數學', 2 union all
Select 5,'李四', '語文', 1 union all
Select 6,'張三', '英語', 2 union all
Select 7,'王五', '語文', 1
-------------------------------------------------------------
Declare @s varchar(2000)
set @s=''
select @s=@s+','+subject+'=isnull((select grade from CC where name=a.name and subject='''+subject+'''),0)' from CC group by SUBJECT
set @s='Select name'+@s+',total=sum(grade) from CC a group by Name'
exec(@s)
--------------結果-------------------
王五 1 1 0 2
李四 0 1 2 3
張三 2 1 2 5
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case subject when '''+subject+''' then grade else 0 end) ['+subject+']'
from t group by subject
exec('select 姓名=name'+@sql+',总分=sum(grade) from t group by name order by name')