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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s