如何描述这个交叉表

[foow]

表 Depart
ID Name,Code
1 北京 010
2 上海 020
3 广州 021
表 Class
ID Name code
1 人口 rk
2 经济 jj
3 文化 wh
4 土地 td
表 Value
ID D_code C_Code Value

1 010 rk 1000
2 020 rk 3402
3 021 rk 2333
4 010 jj 342
5 020 jj 80
6 021 jj 356
如何描述成下列表

ID Name code rk jj wh td
1 北京 010 1000 342 ……
2 上海 020 3402 80 ……
谢谢了

[foow]

回复人： pengdali(大力 V2.0) ( ) 信誉：534 2003-08-20 11:54:00 得分:0


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



执行出来空值的

[pengdali]

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.d_Code group by a.id,a.code,a.name'
exec(@sql)
go

[pengdali]

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)
go

[pengdali]

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)
go

[suowei15]

sdf

[pengdali]

测试：


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)
go

drop table #value,#class,#depart

[pengdali]

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

[pengdali]

如果要：
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

[愉快的登山者]

declare @s varchar(3000)
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)


愉快的登山者

⊙
◢◣◢◣◢◣

[CrazyFor]

参考:
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 a

select @sql = left(@sql,len(@sql)-1) + ' from #'

exec(@sql)

drop table #