insert tmpa values('A', 100 ,0.50)
insert tmpa values('A', 200 ,0.45)
insert tmpa values('A', 800 ,0.30)
insert tmpa values('B', 100 ,0.80)
insert tmpa values('B', 200 ,0.50)
select 0 id,* into tmpb from tmpa
declare @a int,@b varchar(100)
set @a=1
update tmpb set @a=case when @b=物料 then @a+1 else 1 end,@b=物料,id=@a
declare @sql varchar(8000)
set @sql = 'select 物料'
select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量（'+cast(id as varchar)+'）],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格（'+cast(id as varchar)+'）]'
from (select distinct id from tmpb) as a
select @sql = @sql+' from tmpb group by 物料'
print(@sql)
exec(@sql)
go
drop table tmpa ,tmpb
这是我看到的一个行列转换的例子
现在我需要将表tmpb用变量代替 比如
declare @a int,@b varchar(100)
set @a=1
update tmpb set @a=case when @b=物料 then @a+1 else 1 end,@b=物料,id=@a
declare @sql varchar(8000),@tabname varchar(10)
set @tabname = 'tmpb'
set @sql = 'select 物料'
select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量（'+cast(id as varchar)+'）],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格（'+cast(id as varchar)+'）]'
from (select distinct id from tmpb) as a
select @sql = @sql+' from tmpb group by 物料'
print(@sql)
exec(@sql)
go
drop table tmpa ,tmpb
那select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量（'+cast(id as varchar)+'）],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格（'+cast(id as varchar)+'）]'
from (select distinct id from tmpb) as a
这条语句用该怎么实现相应的功能呢？
用类似这种方法exec('select @sql = @sql + ',sum(case id when '+cast(id as varchar)+' then 数量 else 0 end) as [数量（'+cast(id as varchar)+'）],sum(case id when '+cast(id as varchar)+' then 价格 else 0 end) as [价格（'+cast(id as varchar)+'）]'
from (select distinct id from '+@tmpb+') as a')
