How to insert dynamic pivot output result into temp table in SQL server?

Question

I have tried all below links ,But they are not working in my Sever SQL?

SET @cols = STUFF((SELECT ',' + QUOTENAME(AD.MonthFormat) FROM #tempMonthFormat AD FOR xml PATH (''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')
  SELECT @Selectcols =  STUFF((SELECT 
    ',' + ISNULL(@Selectcols + ',','')+ 'ISNULL(' + QUOTENAME(AD.MonthFormat) + ', 0) AS ' + QUOTENAME(AD.MonthFormat)  FROM #tempMonthFormat AD 
  FOR xml PATH (''), TYPE)
  .value('.', 'nvarchar(max)'), 1, 1, '');

Declare @FINALQUERY varchar(max);    
SET @FINALQUERY = '         
                      SELECT  Name,[Issue],' + @Selectcols + ' FROM
                     (
                         SELECT   into #temptable * FROM #Meantimeresult  
                     ) Y
                     PIVOT 
                     (
                         SUM(MT)
                         FOR [Monthformat] IN (' + @cols + ')               
                    ) p GROUP BY ' + @cols+',[Issue], Name'                

        EXEC SP_EXECUTESQL @FINALQUERY;

Dynamic SQL Result INTO #Temp Table

How to create temp table from a dynamic query?

INSERT INTO temp table from unknown number and name of columns (from dynamic PIVOT query) Dynamic Pivot Results to a Temp Table

"Not working" is not a useful comment. What have you tried and what **specifically** is not working? How do you know - did you receive an error? Something else? No one can read your mind. — SMor, Apr 01 '19 at 11:55

score 0 · Answer 1 · answered Apr 01 '19 at 09:27

Can be done with a couple of steps, but the convoluted nature of this answer should indicate to you that you are fighting against sql server here, not with it. Try very hard to find another way to structure your reports so that this is not necessary before undertaking such drastic measures as these.

1) Encapsulate / hack your initial pivot query as a parameterless stored procedure. If you need to pass any data into the procedure you must structure it as a global temp table (## prefixed temp table) e.g.

CREATE PROCEDURE dynamic_meantime_pivot AS
DECLARE 
    @cols nvarchar(max) = STUFF(
        (SELECT ',' + QUOTENAME(AD.MonthFormat) FROM ##tempMonthFormat AD FOR xml PATH (''), TYPE)
        .value('.', 'nvarchar(max)'), 1, 1, '')
    @Selectcols nvarchar(max) = STUFF(
        (SELECT ',' + ISNULL(@Selectcols + ',','')+ 'ISNULL(' + QUOTENAME(AD.MonthFormat) + ', 0) AS ' + QUOTENAME(AD.MonthFormat)  FROM ##tempMonthFormat AD FOR xml PATH (''), TYPE)
        .value('.', 'nvarchar(max)'), 1, 1, '');

Declare @FINALQUERY varchar(max) = '         
                      SELECT  Name,[Issue],' + @Selectcols + ' FROM
                     (
                         SELECT * FROM ##Meantimeresult  
                     ) Y
                     PIVOT 
                     (
                         SUM(MT)
                         FOR [Monthformat] IN (' + @cols + ')               
                    ) p GROUP BY ' + @cols+',[Issue], Name'                

EXEC(@FINALQUERY)

2) Set up any global temp tables required by the above procedure.

3) Call the procedure via the OPENROWSET function as below:

SELECT * INTO #dynamic_meantime_table FROM OPENROWSET('SQLNCLI', 'Server=<your_sql_server_instance_name_here>;Trusted_Connection=yes;',
     'EXEC dynamic_meantime_pivot')

See also this excellent answer regarding OPENROWSET.

Insert results of a stored procedure into a temporary table

How to insert dynamic pivot output result into temp table in SQL server?

1 Answers1