SQL Query: Insert Dynamic Query Results into Temporary Table

I wanted to insert the result of the dynamic PIVOT query into a temporary table and then issue a select clause. I have faced few issues

1. If I have created #Table then I was not able to issue the select statement outside of that statement because #table is not available outside the scope of that statement
2. If I have created ##table then two user could not use that query or stored procedure at the same time.

Benefit of using Temporary table

1. You can use where condition, which you cannot use in PIVOT query

1. Create the temporary table inside the PIVOT scope
2. Issue the select statement  inside the PIVOT scope

set @SqlCommand=N'

WITH ctePivotData AS

(

       select CMPN_Company_Name, APPL_Student_Status, STUD_Student_ID, COUNT(APPL_Student_Status) OVER(PARTITION BY CMPN_Company_Name) AS SchoolTotal

       from STUDstudent

       INNER JOIN CMPN_Company_main

              ON STUDstudent.STUD_School_ISN=CMPN_Company_main.CMPN_ISN

       INNER JOIN APPLications

              ON STUDstudent.STUD_Student_ID=APPLications.APPL_Student_ID

       WHERE CMPN_Company_Name NOT LIKE ' + @Phrase1 + '

       AND APPL_Student_Status NOT IN ('''',''1'',''2'',''97'')

      

)

SELECT CMPN_Company_Name,' + @SpreadingList + ', SchoolTotal INTO #PivotTable

FROM ctePivotData

PIVOT(count(STUD_Student_ID) FOR APPL_Student_Status IN (' + @SpreadingList + ') ) AS P

select * from #PivotTable

;';

execute sp_executesql @SqlCommand;