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' 无效。
慕工程0101907
相关分类