try:
declare @sql varchar(8000)
declare @S_no int
set @S_no =1 --假设站号为1
set @sql='select Year'
select @sql=@sql+',sum(case Month when '''+Month+''' then Rain else 0 end) ['+month+'月]'
from (select distinct Month from tablename) a
select @sql=@sql+',sum(Rain) as 总和 from tablename where s_no='+@S_no+' group by Year'
--print @sql
exec(@sql)
select s_no,year,
sum(case month when 1 then Rain else 0 end) as 1月,
sum(case month when 2 then Rain else 0 end) as 2月,
.
.
.
sum(case month when 12 then Rain else 0 end) as 12月
from table1
group by s_no,year
select s_no,year,
sum(case month when 1 then Rain else 0 end) as 1月,
sum(case month when 2 then Rain else 0 end) as 2月,
.
.
.
sum(case month when 12 then Rain else 0 end) as 12月
group by s_no,year