*********存储过程中行列转换变量怎么使用？*************

[ju_feng]

create table tmpa(物料 varchar(100),数量 int,价格 money)
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')

[ju_feng]

就是实现类似这种功能：
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 (exec('select distinct id from'+ @tabname+')as a')
但是语法不对
各位高手怎么实现？

[pengdali]

create table #临时表(id int)
exec('insert #临时表 select distinct id from '+@tabname)


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 #临时表

[pengdali]

你不要跟着关写个varchar，改为 varchar(10)，print也不要

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(10))+' then 数量 else 0 end) [数量（'+cast(id as varchar(10))+'）],sum(case id when '+cast(id as varchar(10))+' then 价格 else 0 end) [价格（'+cast(id as varchar(10))+'）]'
from (select distinct id from #tmpb) as a
select @sql = @sql+' from #tmpb group by 物料'n

exec(@sql)
go
drop table #tmpa ,#tmpb

[ju_feng]

pengdali(大力 V2.0) 兄功底真是扎实
佩服佩服
但是不知道为什么 exec('select * into #t from tmpb)
select * from #t 是错误的而 create table #临时表(id int)
exec('insert #临时表 select distinct id from '+@tabname)
就是正确的？

[pengdali]

这就和你声明变量一样

这个是对的：
int i;
if (1=2)
{
i=2;
}
i=5;

这个是错的：

if (1=2)
{
int i;
i=2;
}
i=5;

在sqlserver里局部临时表和变量一样是有生命周期的。

[ju_feng]

谢谢！