declare @sql varchar(8000)
set @sql = 'select Province'
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record group by Province' select @sql = @sql + ' union all 'select @sql = 'select ''合计'''
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record'
exec(@sql)
go
set @sql = 'select Province'
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record group by Province' select @sql = @sql + ' union all 'select @sql = 'select ''合计'''
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record'
exec(@sql)
go
set @sql = 'select Province'
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record group by Province' select @sql = @sql + ' union all 'select @sql = 'select ''合计'''
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record'
exec(@sql)
go
select @sql = 'select '合计' as 省份'
select @sql = @sql + ,sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record union select Province'
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record group by Province'
exec(@sql)
go
Sum(ProgramName2) as ProgramName2...... from
(Select Province,Sum(ProgramName1) as ProgramName1,
Sum(ProgramName2) as ProgramName2......
from Dicprovgram Group by province)A Group by 省份
union
Select Province,Sum(ProgramName1) as ProgramName1,
Sum(ProgramName2) as ProgramName2......
from Dicprovgram Group by province
set @sql = 'select Province'
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record group by Province' select @sql = @sql + ' union all 'select @sql = 'select ''合计'''
select @sql = @sql + ',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
select @sql = @sql+' from Record'
exec(@sql)
go
省份 ProgramName1 ProgramName2 ProgramName3...
合计 1200 310 280 -----------> 需要
北京 122 22 15... ----------> 需要
上海 110 20 30... ----------> 需要
广东 100 19 12
.
.
.
set @sql=''
select @sql = @sql +',sum(case vProgramName when '''+vProgramName+''' then 1 else 0 end) ['+vProgramName+']' from DicProvgram
exec('select * from (select Province'+@sql+' from Record group by Province union all select ''合计'''+@sql+' from record ) tem order by programName1 desc')
go
create table test12 (id int,name varchar(50),league int)insert into test12 values(1,'a',2)
insert into test12 values(1,'a',6)
insert into test12 values(2,'b',9)
insert into test12 values(2,'b',3)
insert into test12 values(2,'b',8)declare @sql varchar(1000),@i int,@sql2 varchar(1000)
select @i=0,@sql='select name',@sql2=''
select @i=@i+1,@sql = @sql + ',min(case league when '''+convert(varchar,league)+''' then league end) [社团名称'+cast(@i as varchar(10))+']'
,@sql2=@sql2+',cast(count(社团名称'+cast(@i as varchar(10))+') as varchar(1000))' from test12select @sql=@sql+' ,sum(league) as ''小计'' into #a from test12 group by name',@sql2=@sql2+',cast(sum(小计) as varchar(100))'
exec (@sql+ ' select * from #a union all select ''合计'''+@sql2+' from #a')
drop table test12