CODEWITHSUNDEEP
sqlsql-serverpivottemp-tablesdynamicquery
Phoenix
Phoenix

Reputation: 263

insert result of dynamic pivot query into temp table

I have a dynamic pivot query

     SELECT * FROM (SELECT [SERVICEID],
                    [SERVICETYPEID],[TRAIN],[SERVICETYPEOPTIONID], 
                    [OPTIONNAME], [CLIENTID],[AGENT],  [MANAGEMENTLEGID ],   
                    [LEG],   [ALLOCATIONDATE],[CURRENTALLOCATION] 
   FROM EXCELEXPORT) AS [SubTable]  PIVOT (MAX([CURRENTALLOCATION])FOR [ALLOCATIONDATE] IN ( [02/05/19], [02/07/19], [02/08/19], [02/09/19], [02/10/19]) ) AS [Pivot];

which needs to be inserted into temp table.How can i achieve it.Any inputs would be valauble.

Upvotes: 1

Views: 629

Answers (2)

gotqn
gotqn

Reputation: 43656

If you want to insert the result of EXEC sp_executesql into a temporary table, you need to first defined the definition of your table.

And as your dynamic T-SQL statement can lead to different numbers of columns returned you are not about to define the table in advanced.

Actually, you can define such table in advance using T-SQL statement, but it not be visible in other sp_execuetsql execution or the outer scope:

EXEC sp_executesql N'CREATE TABLE #DataSource ([column] int)';

EXEC sp_executesql N'SELECT * FROM #DataSource';

SELECT *
FROM #DataSource;

So, the only choice you have is to wrap all of your logic in dynamic T-SQL statement and then return the result.

Upvotes: 1

Muhammad Waheed
Muhammad Waheed

Reputation: 1088

A PIVOT used to rotate the data from one column into multiple columns.

Here is DEMO using temp table to deal with PIVOT query

Upvotes: 0

Related Questions