Invalid object name #temp0

May be you haven't create the #temp0 table. You have to create the temp table before using it.

Please check below sample query, this may help you.

IF OBJECT_ID('tempdb..#temp') != 0
DROP TABLE #temp
CREATE TABLE #temp(Location nvarchar(max),
Quantity numeric(19,6), ItemName nvarchar(50))
insert into #temp values('Delhi',22.405000,'item1')
insert into #temp values('Delhi',14.405000,'item2')
insert into #temp values('Delhi',10.405000,'item3')
insert into #temp values('Bhubaneswar',4.405000,'item1')
insert into #temp values('Bhubaneswar',34.405000,'item2')
declare @cols as nvarchar(max), @cols1 as nvarchar(max);
declare @query as nvarchar(max)='';
DECLARE @columns VARCHAR(MAX)
select @cols = coalesce(@cols + ', ', '') + ItemName + ' = SUM(ISNULL([' + ItemName + '], 0))' from (Select distinct ItemName from #temp) as tmp
select @cols1 = coalesce(@cols1 + ', ', '') + ItemName from (Select distinct ItemName from #temp) as tmp
SELECT @columns = COALESCE(@columns + ', ', '') + '[' + ItemName + '] numeric(19,6)' from (Select distinct ItemName from #temp) as tmp
IF OBJECT_ID('tempdb..#temp0') != 0
DROP TABLE #temp0
CREATE TABLE #temp0(
Location nvarchar(max)
)
EXEC('alter table #temp0 add ' + @columns)
set @query = 'insert into #temp0 select Location, ' + @cols + ' from (select Location,Quantity,ItemName from #temp) x pivot
( Sum(quantity) for ItemName in (' + @cols1 + ')) piv GROUP BY Location';
exec(@query)
select * from #temp0

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
1. IF OBJECT_ID('tempdb..#temp0') IS NOT NULL DROP TABLE #temp0
2. GO
3. set @query = 'select Route, ' + @cols + ' into #temp0 from (select Loc,Quantity,name from #temp) x pivot ( Sum(quantity) for name in (' + @cols + ')) piv '; select * from #temp0