INSERT INTO Temp Table Dynamic Pivot

Question

I've the following t-sql:

select      d.OutageType + '/' + e.Facility Outage,
            c.MaterialScopeCode, 
            count(a.ItemNo) CountofItemNo
into        #tt
from        _OutageMaterial a
            inner join _OutageSchedule b on a.OutageScheduleID = b.OutageScheduleID
            inner join _MaterialScope c on a.MaterialScopeID = c.MaterialScopeID
            inner join _OutageType d on b.OutageTypeID = d.OutageTypeID
            inner join _Facility e on b.FacilityID = e.FacilityID
where       ReqQty <> 0
group by    c.MaterialScopeCode, e.Facility, d.OutageType
order by    d.outagetype, e.Facility, c.MaterialScopeCode


-- /// RESULT /// --
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT      @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Outage)
FROM        (
            SELECT DISTINCT         Outage 
            FROM                    #tt
            ) AS Scope

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT MaterialScopeCode as Scope, ' + @ColumnName + '
FROM #tt
PIVOT(SUM(CountofItemNo) 
FOR Outage IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

I've tried many times, but still .. Error. I could not select the temp table because it doesn't created. Invalid Object. I want to put the result of dynamic pivot into temp table.
How can i do that? Please advise.

Thank you.

Are you trying to create temp table inside dynamic query. If yes where is that part — Pரதீப், Nov 12 '15 at 07:09
I want to put the result of dynamic pivot into #temp table then select it. — Haminteu, Nov 12 '15 at 07:14
Try this in your dynamic query `FROM #tt into ##temp PIVOT(SUM(CountofItemNo)`. **Note :** This will throw error when query is executed parallely in multiple session — Pரதீப், Nov 12 '15 at 07:28

score 3 · Accepted Answer · answered Nov 12 '15 at 08:14

what @MM93 told you, that is correct answer, but here is full code

IF OBJECT_ID('TEMPDB.dbo.##TempTableTesting') IS NOT NULL DROP TABLE ##TempTableTesting

declare @sql nvarchar(300) = 'SELECT MaterialScopeCode as Scope, ' + @ColumnName + '
into ##TempTableTesting
FROM #tt
PIVOT(SUM(CountofItemNo) 
FOR Outage IN (' + @ColumnName + ')) AS PVTTable'

execute sp_executesql @sql

select * from ##TempTableTesting

So in your case you have to use ##TempTable as those tables (global) are available to ALL sessions, #TempTable won't work in your case

score 1 · Answer 2 · edited May 23 '17 at 12:23

1

I answered this here so I won't duplicate but the short answer is that it is possible to insert the results of a dynamic pivot into a local (not global) temp table, you just need to create the temp table with a single column, and then add the other columns dynamically.

edited May 23 '17 at 12:23

Community

1
1

answered Feb 20 '16 at 22:04

KnarfaLingus

456
2
10

INSERT INTO Temp Table Dynamic Pivot

2 Answers2