如何把EXEC(@SQL)得到的结果集(列是动态的)插入到一张临时表
1
悬赏园豆：100
[已解决问题]
解决于 2010-11-09 11:25
if object_id('tempdb.dbo.#TotalSalesForMonth') is not null
drop table #TotalSalesForMonth --删除临时表
select * into #TotalSalesForMonth
from (
select
cast(month(CreateDate) as nvarchar) as monthN,
sum(BaseQuantity) as total
from (
select a.CreateDate,b.BaseQuantity
from dbo.T_Sales_Order a left join dbo.T_Sales_Product as b
on a.Id =b.ParentId
where year(a.CreateDate)=year(getdate())
) c
group by month(CreateDate)
) p
DECLARE @str VARCHAR(500) ,@Sql NVARCHAR(max)
SET @str=''
SELECT @str=@str+','+'['+cast(monthN as nvarchar)+']' FROM #TotalSalesForMonth
SET @str=right(@str,len(@str)-1)
--由于pivot 不支持 在in ()中直接添加字符串，所以需要使用 sql 拼接一下
SET @Sql='select * from #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
drop table #ForMonth --删除临时表
insert into #ForMonth exec(@Sql)
(1 行受影响)
消息 208，级别 16，状态 0，第 25 行
对象名 '#ForMonth' 无效。
最佳答案
0
insert into #ForMonth exec(@Sql)使用这样的SQL语句，首先是表#ForMonth必须存在，如果不存在这个表，要用select * into #ForMonth from 表名
你可以这样写试一下（只后面几句，前面的不变）
SET @Sql='select * into #ForMonth from #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
drop table #ForMonth --删除临时表
exec(@Sql)
收获园豆：100
紧接着你的语句后面
Select * from #ForMonth
依然会报错
消息 208，级别 16，状态 0，第 26 行
对象名 '#ForMonth' 无效。
--
其他回答(1)
0
create table T_Sales_Order
(
id int,
createdate datetime
)
go
create table T_Sales_Product
(
parentid int,
BaseQuantity int
)
go
if object_id('tempdb.dbo.#TotalSalesForMonth') is not null
drop table #TotalSalesForMonth --删除临时表
select * into #TotalSalesForMonth
from (
select
cast(month(CreateDate) as nvarchar) as monthN,
sum(BaseQuantity) as total
from (
select a.CreateDate,b.BaseQuantity
from dbo.T_Sales_Order a left join dbo.T_Sales_Product as b
on a.Id =b.ParentId
where year(a.CreateDate)=year(getdate())
) c
group by month(CreateDate)
) p
DECLARE @str VARCHAR(500) ,@Sql NVARCHAR(max)
SET @str=''
SELECT @str=@str+','+'['+cast(monthN as nvarchar)+']' FROM #TotalSalesForMonth
SET @str=right(@str,len(@str)-1)
--由于pivot 不支持 在in ()中直接添加字符串，所以需要使用 sql 拼接一下
SET @Sql='select * into #ForMonth from #TotalSalesForMonth pivot (sum(total) for monthN in ('+@str+') ) as pvt '
if object_id('tempdb.dbo.#ForMonth') is not null
drop table #ForMonth --删除临时表
exec(@Sql)
表中数据分别为T_Sales_Order：
| 1 | 2010-11-8 0:00:00 |
| 2 | 2010-11-8 0:00:00 |
| 3 | 2010-11-8 0:00:00 |
T_Sales_Product：
| 1 | 22222 |
| 3 | 44444 |
