declare @sql varchar(8000)
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.D_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.c_Code group by a.id,a.code,a.name'
exec(@sql)
go
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.D_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.c_Code group by a.id,a.code,a.name'
exec(@sql)
go
ID Name code 人口 经济 文化 土地
1 北京 010 1000 342 ……
2 上海 020 3402 80 ……--------------------------------------------
declare @sql varchar(8000)
set @sql = 'select a.id,a.[name],a.code'
select @sql = @sql + ',sum(case b.D_code when '''+code+''' then b.Value else 0 end) ['+[Name]+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.c_Code group by a.id,a.code,a.[name]'
exec(@sql)
go
set @s = 'select Value.ID, Depart.Name, Value.D_code code'
select @s = @s + ',sum(case when C_Code ='''+ Code + ''' then Value else 0 end) as [' +name+']' from Class
set @s = @s + ' from Value, Depart where Value.D_code = Depart.Code group by Value.ID, Depart.Name, Value.D_code'
exec (@s)
愉快的登山者 ⊙
◢◣◢◣◢◣
create table #(a varchar(100),b int)
insert # values('aa',11)
insert # values('bb',1)
insert # values('aa',45)
insert # values('cc',81)
insert # values('a',11)
insert # values('aay',561)
insert # values('a',14)declare @sql varchar(8000)
set @sql = 'select '
select @sql = @sql + 'sum(case a when '''+a+'''
then b else 0 end) '+a+'的数量,'
from (select distinct a from #) as aselect @sql = left(@sql,len(@sql)-1) + ' from #'exec(@sql)drop table #
declare @sql varchar(8000)
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.D_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.c_Code group by a.id,a.code,a.name'
exec(@sql)
go
执行出来空值的
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.D_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.d_Code group by a.id,a.code,a.name'
exec(@sql)
go
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.c_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.d_Code group by a.id,a.code,a.name'
exec(@sql)
go
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.c_code when '''+code+''' then b.Value else 0 end) ['+code+']' from Class
select @sql = @sql+' from Depart a,Value b where a.Code=b.d_Code group by a.id,a.code,a.name'
exec(@sql)
go
create table #Depart(ID int,Name varchar(10),Code varchar(10))
insert #depart values(1,'北京','010')
insert #depart values(2,'上海','020')
insert #depart values(3,'广州','021')create table #Class(ID int,Name varchar(10),code varchar(10))
insert #class values(1,'人口','rk')
insert #class values(2,'经济','jj')
insert #class values(3,'文化','wh')
insert #class values(4,'土地','td')
create table #Value(ID int,D_code varchar(10),C_Code varchar(10),Value int)
insert #value values(1,'010','rk', 1000)
insert #value values(2,'020','rk', 3402)
insert #value values(3,'021','rk', 2333)
insert #value values(4,'010','jj', 342)
insert #value values(5,'020','jj', 80)
insert #value values(6,'021','jj', 356)
declare @sql varchar(8000)
set @sql = 'select a.id,a.name,a.code'
select @sql = @sql + ',sum(case b.c_code when '''+code+''' then b.Value else 0 end) ['+code+']' from #Class
select @sql = @sql+' from #Depart a,#Value b where a.Code=b.d_Code group by a.id,a.code,a.name'
exec(@sql)
godrop table #value,#class,#depart