I have a stored procedure where I am running/executing a declared variable which is set to a select query for pivot table, important thing is that that pivot table is transforming column to n number of rows. Yes the rows are not fixed that is why I have problem in creating a temp table with fixed column. What I want is that create a temp table by executing the execute command so the columns would be determined by the select/insert statement. Below is the query.
SELECT @cols = @cols + QUOTENAME(item) + ',' FROM (select distinct item from #TableData ) as tmp select @cols = substring(@cols, 0, len(@cols)) set @tequery = 'SELECT * from ( select Sno, item, SoldValue from #TableData ) mytbl pivot ( max(ValueInDhs) for GroupName in (' + @cols + ') ) newTable' execute(@tablequery)
I want to store the result of @tequery into a temp table or better this stored procedure should return the result set instead of int as this procedure I have inserted into my entity framework where its showing(after insert) its return type as int.
You could do this with a global (##) temp table, although you'd have to be careful of multiple processes doing this at the same time. Here is an abitrary example:
declare @sql varchar(max) = ' select * into ##temp -- create global temp table from ( select name , create_date = convert(varchar(4), create_date, 102) from sys.objects) as o pivot ( count(name) for create_date in (,,, ,) ) as p; ' exec (@sql) -- if you do not want to use the global table, select it into a temp table select * into #temp from ##temp -- and drop the global drop table ##temp select * from #temp