tsql - dynamically create and load table from select query

Question

SELECT 
    MEM_ID, [C1],[C2] 
from  
   (select 
        MEM_ID, Condition_id, condition_result 
    from tbl_GConditionResult 
   ) x 
pivot  
   ( 
      sum(condition_result) 
      for condition_id in ([C1],[C2]) 
   ) p 

The above query returns three columns of data. Until runtime I will not know how many columns in the select statement. Is it possible to load the data from the select statement into a dynamically created table? After processing the data from the dynamically created table I want to drop the table.

Thank you for your help.

Smith

Answer 1

Yes, do a SELECT INTO e.g.

SELECT 
    MEM_ID, [C1],[C2] 
into #TEMP
from  
   (select 
        MEM_ID, Condition_id, condition_result 
    from tbl_GConditionResult 
   ) x 
pivot  
   ( 
      sum(condition_result) 
      for condition_id in ([C1],[C2]) 
   ) p 

-- Do what you need with the TEMP table

DROP TABLE #TEMP