1

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.

Phoenix
  • 263
  • 1
  • 3
  • 13
  • what is the purpose of the `temp table` ? Can you just return the result set back to the calling application ? – Squirrel Jan 15 '19 at 06:11
  • because i will be updating certain fields in temp table. – Phoenix Jan 15 '19 at 07:33
  • You need to reconsider how you want to do all this. What you want is not possible with temp table. Once it gets out of scope it is gone. One alternative is to use global temp table but you will need to use a create the global temp table with dynamic name else it will conflict – Squirrel Jan 15 '19 at 07:39

2 Answers2

1

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.

gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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

Here is DEMO using temp table to deal with PIVOT query

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30