交叉表应用示例

create table A
(
Name varchar(10),
Province varchar(20),
Score int
)
insert A
select '李三','四川',5 union
select '小王','四川',3 union
select '小張','廣州',3 union
select '李三','廣州',2 union
select '小張','湖南',3 union
select '李三','湖南',4

--测试
declare @s varchar(8000)
set @s = ''

select @s = @s +','+Province+'= sum(case province when'''+province+'''then score else 0 end)'
from A group by Province order by Province

exec ('select name '+@s+',sum(Score) As Total from A group by Name order by Name')