<div>
create new table &nbsp;with the below list of columns. 
SELECT 
&nbsp; &nbsp; &nbsp;CoursePatId 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,MIN(FirstTreatStart1) FirstTreatStart 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,SUM(NumberOfTreatments) NumberOfTreatments 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,MAX([Check-point-inhib])[Check-point-inhib] 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,MAX([Cytokine]) [Cytokine] 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,MAX([Cytostatic drug]) [Cytostatic drug] 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,MAX([Targeted]) [Targeted] 
FROM ..... 
 
and in the procedure first TRUNCATE &nbsp;this TABLE and insert &nbsp;new rows in it. 
 
e.g 
INSERT INTO newtable 
SELECT * FROM table1 WHERE condition; -- This should be your old/exiting &nbsp;query.
</div>

<div>
Yes, that is possible. However we need to fix the SP so it doesnt error. 
 
The challenge will be the variable number of columns in the result set. But it would be easier if saved as a normal table, then, run the pivot on that table. The 'Flush' option would mean generate a new set of data, otherwise, use the data in the table.
</div>

<div>
be sure about the columns or the query you are trying into insert.
</div>

<div>
Hello Author, 
 
Basically we have to drop the existing table first and create it again on the fly as out number and names of columns are dynamic in nature. 
 
I have added below lines.. 
 
/*Drop the table if already exists*/ 
--IF OBJECT_ID('tempdb..YourPhysicalTableName') IS NOT NULL 
-- &nbsp; &nbsp; DROP TABLE YourPhysicalTableName; &nbsp; 
 
/*INTO YourPhysicalTableName*/ 
--Insert new data INTO YourPhysicalTableName 
 
/*SELECT DATA FROM YourPhysicalTableName*/ 
--SELECT * FROM YourPhysicalTableName; 
 
Changed below code for this requirement. 
 

<pre><code class="code-10 nolinks" id="code-20-42405273-1">--

USE [mcd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_q3TreatmentRelevant_Crosstab] 
AS

/*Declare Variables*/
DECLARE @vals AS VARCHAR(MAX) = ''
DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
SELECT 
 @vals = @vals+ '['+ TreatmentDrugHighLevel +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ TreatmentDrugHighLevel +']) ' + '['+ TreatmentDrugHighLevel +']' + ',' 
FROM ( SELECT DISTINCT TreatmentDrugHighLevel FROM [dbo].[view_3TreatmentRelevant] ) [view_3TreatmentRelevant]

/*Remove last comma*/
SELECT @vals = SUBSTRING(@vals,1,LEN(@vals)-1)
SELECT @Maxvals = ',' + SUBSTRING(@Maxvals,1,len(@Maxvals)-1)

IF OBJECT_ID('tempdb..YourPhysicalTableName') IS NOT NULL
 DROP TABLE YourPhysicalTableName 

/**Generate dynamic SQL*/
DECLARE @SQL AS VARCHAR(MAX) = '

SELECT 
 CoursePatId 
 ,MIN(FirstTreatStart1) FirstTreatStart
 ,SUM(NumberOfTreatments) NumberOfTreatments
 ' + @MaxVals + '

INTO YourPhysicalTableName
FROM
(
 SELECT 
 dbo.view_3TreatmentRelevant.CoursePatID, 
 MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
 MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart1,
 COUNT(dbo.view_3TreatmentRelevant.PatSiteID) as NumberOfTreatments,
 dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
 FROM 
 dbo.view_3TreatmentRelevant
 GROUP BY
 dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel
) AS BaseData
PIVOT 
(
 MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN (' + @Vals + ') 
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID'

/*Execute the dynamic string*/
EXEC ( @SQL )

/*SELECT DATA FROM YourPhysicalTableName*/
SELECT * FROM YourPhysicalTableName;

--
</code></pre>
</div>

<div>
All self contained in your stored procedure. We add an optional parameter @flush to tell it to flush / refresh 'Y' or not 'N'. 
 
If not flushing / refresh, then use 'N' to display existing content 
 
Now, we have used a file named TMP_PIVOT_RESULTS and you may want to change that to something more robust / unique. 
 
And to run : 
 
 
exec [proc_q3TreatmentRelevant_Crosstab] 'N' &nbsp;-- No flush = display whatever is in the table, if it exists, if not exists then build it anyway. 
or 
 
exec [proc_q3TreatmentRelevant_Crosstab] &nbsp; &nbsp;-- same as 'N' 
 
or 
 
exec [proc_q3TreatmentRelevant_Crosstab] 'Y' &nbsp; -- to reset / flush by building a new table etc... 
 
 
 
 

<pre><code class="code-10 nolinks" id="code-20-42405349-1">ALTER PROCEDURE [dbo].[proc_q3TreatmentRelevant_Crosstab] (@flush char(1) = 'N') 
AS
BEGIN

 set nocount on
 SET ANSI_WARNINGS OFF

 IF @flush = 'N' and object_id('tmp_pivot_results','U') is not null
 begin 
 select * from tmp_pivot_results
	return
 end

 -- print 'flushing'

 if object_id('tmp_pivot_results','U') is not null drop table tmp_pivot_results

/*Declare Variables*/
 DECLARE @vals AS VARCHAR(MAX) = ''
 DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
SELECT 
 @vals = @vals+ '['+ TreatmentDrugHighLevel +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ TreatmentDrugHighLevel +']) ' + '['+ TreatmentDrugHighLevel +']' + ',' 
FROM [dbo].[view_3TreatmentRelevant]
GROUP BY TreatmentDrugHighLevel

/*Remove last comma*/
SELECT @vals = SUBSTRING(@vals,1,LEN(@vals)-1)
SELECT @Maxvals = ',' + SUBSTRING(@Maxvals,1,len(@Maxvals)-1)

--PRINT @vals
--PRINT @MaxVals

/**Generate dynamic SQL*/
DECLARE @SQL AS VARCHAR(MAX) = '

SELECT 
 CoursePatId 
 ,MIN(FirstTreatStart1) FirstTreatStart
 ,SUM(NumberOfTreatments) NumberOfTreatments
 ' + @MaxVals + '
into tmp_pivot_results
FROM
(
 SELECT 
 dbo.view_3TreatmentRelevant.CoursePatID, 
 MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
 MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart1,
 COUNT(dbo.view_3TreatmentRelevant.PatSiteID) as NumberOfTreatments,
 dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
 FROM 
 dbo.view_3TreatmentRelevant
 GROUP BY
 dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel
) AS BaseData
PIVOT 
(
 MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN (' + @Vals + ') 
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

 select * from tmp_pivot_results
'

/*Execute the dynamic string*/
 EXEC ( @SQL )

END
GO
</code></pre>
 
 
Now this was created using the latest version of SQL Server. The reason why it works is using the exec(@sql). You cannot normally create or delete then populate a table like that. 
 
We should make it a lot more robust. 
 
Cheers, 
Mark Wills
</div>

<div>
Mark... that is like magic to me. ;-) &nbsp;So cool!!!! Now... a little additional question: 
 
1) If I want to run the SP via a website, how will the Y/N work? 
2) &nbsp;If I don't want that Y/N option but just launch the SP, how much shall I delete from the script? 
 
Happy regards 
 
Ulrich
</div>

<div>
Thank you again. I'll open another question related to this &nbsp;one. :-)
</div>

<div>
My pleasure. Happy to have been able to help you. 
 
Cheers, 
Mark Wills
</div>

<div>
<div class="content wysiwyg-content">
Hi there. 
 
I'm working on a stored procedure that makes a pivot table. I want to build into the stored procedure the option to save the result to a table only for this particular output. And the table must be flushed before a new query result from the stored procedure is generated. 
Is that possible? Any example? :-) 
It's the procedure in my other question here: <a href="https://www.experts-exchange.com/questions/29073732/Convert-MS-Access-SQL-Pivot-code-to-MS-SQL-Server-code.html" rel="ugc">29073732/Convert-MS-Access-SQL-Pivot-code-to-MS-SQL-Server-code.html</a> 
 
Best regards 
 
Ulrich
</div>
</div>

Hi there.

I'm working on a stored procedure that makes a pivot table. I want to build into the stored procedure the option to save the result to a table only for this particular output. And the table must be flushed before a new query result from the stored procedure is generated.
Is that possible? Any example? :-)
It's the procedure in my other question here: 29073732/Convert-MS-Access-SQL-Pivot-code-to-MS-SQL-Server-code.html (https://www.experts-exchange.com/questions/29073732/Convert-MS-Access-SQL-Pivot-code-to-MS-SQL-Server-code.html)

Best regards

Ulrich

Save query generated from stored procedure to table?

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.