Getting a Dynamically-Generated Pivot-Table into a Temp Table

Getting a Dynamically-Generated Pivot-Table into a Temp Table

you could do this:

-- add 'loopback' linkedserver 
if exists (select * from master..sysservers where srvname = 'loopback')
    exec sp_dropserver 'loopback'
go
exec sp_addlinkedserver @server = N'loopback',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername
go

declare @myDynamicSQL varchar(max)
select @myDynamicSQL = 'exec sp_who'
exec('
    select * into #t from openquery(loopback, ''' + @myDynamicSQL + ''');
    select * from #t
    ')

EDIT: addded dynamic sql to accept params to openquery

Inserting dynamic pivot Result into Temp table

Try this Query...

First Drop the #Final if exists....

then create the #Final table...

then Perfrom your old Query.....will not give error Invalid object name '#Final'. this type of error

CREATE TABLE #TBL  (Dates DATE, [Year] INT, Amt MONEY,Item Varchar(100))
INSERT INTO #TBL VALUES 
('2018-06-01',2018,34,'Milk'),('2018-07-01',2018,99,'Rice'),('2018-08-01',2018,77,'Rice'),('2018-09-01',2018,26,'Rice'),
('2018-10-01',2018,75,'Orange'),('2018-11-01',2018,94,'Grapes'),('2018-12-01',2018,80,'Grapes'),('2019-01-01',2019,9,'Grapes'),
('2019-02-01',2019,52,'Milk'),('2019-03-01',2019,28,'Orange'),('2019-04-01',2019,61,'Orange'),('2019-05-01',2019,51,'Milk');

IF OBJECT_ID('tempdb..#Final', 'U') IS NOT NULL
/*Then it exists*/
DROP TABLE #Final

CREATE TABLE #Final  ([Year] INT, Amt MONEY,Item Varchar(100))

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME( Item) 
            FROM #TBL 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 SET @query = 
      'SELECT 
       * 
       INTO #Final
       FROM (
     SELECT [Year],Amt ,Item
    FROM #TBL ) FG
    PIVOT
    (
    SUM(Amt) FOR Item IN ('+@cols+')
    ) pv'
EXEC(@query)
select * from #Final

DROP TABLE #TBL

Storing dynamic Pivot result into a temporary table in SQL Server

If you are using apply then why you need further same logic in PIVOT (i.e. Channel + CONVERT(Varchar(4), Year)) which is already available in apply.

So, i would use Value instead in PIVOT :

. . . 
Pivot (sum([Payments]) For [Value] in ([HV2012],[HV2013],[HV2014],[NL2012]) ) p,

So, your updated Dynamic SQL would be :

Declare @SQL varchar(max) = '    
if object_id(''tempdb..##TempTable'') is not null
begin
    drop table ##TempTable
end

create table ##TempTable([Id] int null, ' + 
            Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year)) + ' Varchar(20) null'            
            From [dbo].MyTable
            Order By 1 
            For XML Path('')),1,1,'')+ ')
INSERT INTO ##TempTable
Select *
 From (
        Select A.ID, A.Payments
              ,B.*
         From  [dbo].MyTable a 
         Cross Apply ( values ( Channel + CONVERT(Varchar(4), Year)
                     )) B ([Value])
      ) S
 Pivot (sum([Payments]) For [Value] in 
 (' + Stuff((Select Distinct ','+QuoteName(Channel + CONVERT(Varchar(4), Year))                                                               
                                               From #tm
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'

print @sql

Exec(@SQL)

SELECT * FROM ##TempTable

I have made no of changes as there are many correction needs to be done prior to execution.

INSERT INTO Temp Table Dynamic Pivot

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

Dynamic Pivot using Temp tables

To get each Dx_Code of a Patient_Account in the same line, you need to GROUP BY Patient_Account, and use some aggregate function (like MIN() or MAX()) to get the Dx_Code and Dx_Desctiption.

Another way to make a dynamic query:

SELECT STRING_AGG(query_piece, '')

FROM (

(SELECT 'SELECT Patient_Account,' AS query_piece)

UNION ALL

(SELECT 
    CONCAT('MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Code END) AS ', Dx_Rank, ', ', 
    'MIN(CASE WHEN Dx_Rank = ''', Dx_Rank, ''' THEN Dx_Desctiption END) AS ', Dx_Rank, '_Description, ')
FROM Diagnosis
GROUP BY Dx_Rank)

UNION ALL

(SELECT 'Visit_Key 
    FROM Diagnosis
    GROUP BY Patient_Account, Visit_Key;')) AS dynamic_query;

The result of the dynamic query is the query you have to execute to get the desired result:

SELECT Patient_Account,MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Code END) AS Dx_1, MIN(CASE WHEN Dx_Rank = 'Dx_1' THEN Dx_Desctiptio END) AS Dx_1_Description, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Code END) AS Dx_2, MIN(CASE WHEN Dx_Rank = 'Dx_2' THEN Dx_Desctiptio END) AS Dx_2_Description, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Code END) AS Dx_3, MIN(CASE WHEN Dx_Rank = 'Dx_3' THEN Dx_Desctiptio END) AS Dx_3_Description, Visit_Key 
    FROM Diagnosis
    GROUP BY Patient_Account, Visit_Key;

Output with the example data in your question:

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(QT.QUESTION_DESC)
                 FROM #QUES_TEMP QT
                 GROUP BY QT.QUESTION_DESC
                 FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)')
                 ,1,1,'')

set @query = 'SELECT EVAL_ID, AuditType, ' + @cols + ' from
        (
            select     QT.EVAL_ID,
                       QT.AuditType,
                       QT.SCORE,
                       QT.QUESTION_DESC
            into ##tmp
            from #QUES_TEMP QT
         ) x

        pivot
        (
             max(SCORE)
             for QUESTION_DESC in (' + @cols + ')
        ) p '

execute(@query);

SELECT * FROM ##tmp

SELECT @query = 
'
SELECT
* into ##temp
FROM 
(SELECT
Count(dbo.InventoryBiltyMaster.ID) AS BiltyCount,
--dbo.InventoryBiltyMaster.InventoryProductMasterID,
--dbo.InventoryBiltyMaster.VehicleMasterID,
dbo.InventoryProductMaster.Name,
dbo.VehicleMaster.VehicleNumber

FROM
dbo.InventoryBiltyMaster
INNER JOIN dbo.InventoryPartyProductPriceMaster ON dbo.InventoryBiltyMaster.InventoryPartyProductPriceMasterID = dbo.InventoryPartyProductPriceMaster.ID
INNER JOIN dbo.InventoryProductMaster ON dbo.InventoryPartyProductPriceMaster.InventoryProductMasterID = dbo.InventoryProductMaster.ID
INNER JOIN dbo.VehicleMaster ON dbo.InventoryBiltyMaster.VehicleMasterID = dbo.VehicleMaster.ID
WHERE
dbo.InventoryBiltyMaster.PartyMasterID = ' + CAST(@partymasterid as nvarchar(50)) + '
GROUP BY
dbo.InventoryBiltyMaster.InventoryProductMasterID,
dbo.InventoryProductMaster.Name,
dbo.InventoryBiltyMaster.VehicleMasterID,
dbo.VehicleMaster.VehicleNumber
)
AS S
PIVOT
(
    MAX(BiltyCount)
    FOR [Name] IN (' + LEFT(@cols, LEN(@cols)-1) + ')
)AS pvt';

EXEC SP_EXECUTESQL @query
--now you can use ##temp

 set @query = 'SELECT   '+@cols+' into  ##temp1 from 
    (
        select Columnname, Rownum 
            , value
            from #temp
   ) x
    pivot 
    (
         max(Value)
        for Columnname in (' + @cols + ')
    ) p '

execute(@query) 
select * from  ##temp1    

Age   Email         Name    Phone
20  Bob@gmail.com   Ben     888888888
20  Rob@gmail.com   Bob     999999999
25  NULL            Rob     NULL