然后把所有出现得身份都建一行,然后在tb1表里面Province列改成Province_id列
然后就可以使用下面得语句了
select name,四川=sum(case Province_id when 1 then Score end),
廣州=sum(case Province_id when 2 then Score end),--这里你有几个省就列几个
湖南=sum(case Province_id when 3 then Score end),
总计=sum(score) from tbl order by name
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 @sql varchar(1000)
select @sql=''
select @sql=@sql+',sum(case when Province='''+Province+''' then Score else 0 end) as '+quotename(Province)
from A group by Province
select @sql='select Name'+@sql+',sum(Score) as 总计 from A group by Name order by Name'
exec(@sql)
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')
select name,sum(case province when '廣州'then score else 0 end) as 廣州 ,
sum(case province when '湖南'then score else 0 end) as 湖南 ,
sum(case province when '四川'then score else 0 end) as 四川
from a group by a.name