Convert MS Access SQL Pivot code to MS SQL Server code...

Hi there.

I am trying to convert some pivot queries from MS Access to MS SQL Server. I'm almost there, but not completely as it has to be.

This is the SQL code from MS Access:

TRANSFORM Min(q3TreatmentRelevant.TreatStart) AS MinOfTreatStart
SELECT q3TreatmentRelevant.CoursePatID, Min(q3TreatmentRelevant.TreatStart) AS FirstTreatStart, Count(q3TreatmentRelevant.SiteID) AS NumberOfTreatments
FROM q3TreatmentRelevant
GROUP BY q3TreatmentRelevant.CoursePatID
PIVOT q3TreatmentRelevant.TreatmentDrugHighLevel;

Open in new window


And here's what I got to with SQL Server:

SELECT * FROM
(SELECT 
	dbo.view_3TreatmentRelevant.CoursePatID, 
	MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
	dbo.view_3TreatmentRelevant.PatSiteID,
	dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
FROM 
	dbo.view_3TreatmentRelevant
GROUP BY
	dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
) AS BaseData
PIVOT (
	MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN ([Check-point-inhib]
,[Cytokine]
,[Cytostatic drug]
,[Targeted])
	) AS PivotTable
ORDER BY CoursePatID

Open in new window


The result looks like this (sorry for lack of allignment):

CoursePatId	NumberOfTreatments	Check-point-inhib	Cytokine		Cytostatic drug		Targeted
a		1			2010-10-27 00:00:00.000	NULL			NULL			NULL
a		2			NULL			NULL			2010-03-15 00:00:00.000	NULL
b		1			2010-06-23 00:00:00.000	NULL			NULL			NULL
c		1			2010-07-01 00:00:00.000	2008-12-01 00:00:00.000	NULL			NULL

Open in new window


1)  I want to group by CoursePatId only and get only 1 line for each, but it split up for several ones like the "a".
2)  I don't like using static values inside the "PIVOT  FOR  IN" clause. I would prefer dynamic values from a SELECT statement, but that doesn't work. How can I do this?
3)  All of it gotta be a view. Is that possible considering 2), or will I have to make a stored procedure?

Best regards

Ulrich
ullenulleAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Please try this...

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 [dbo].[view_3TreatmentRelevant]

/*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 + '
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 )

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
changing code for you.

stored procedure is better than view. so lets go with that.
0
 
Pawan KumarDatabase ExpertCommented:
>>1)  I want to group by CoursePatId only and get only 1 line for each, but it split up for several ones like the "a".
Please verify this data. If fine , will make it dynamic.

SELECT 
     CoursePatId 
	,SUM(NumberOfTreatments) NumberOfTreatments
	,MAX([Check-point-inhib])[Check-point-inhib]
	,MAX([Cytokine]) [Cytokine] 
	,MAX([Cytostatic drug]) [Cytostatic drug]
	,MAX([Targeted]) [Targeted] 
FROM
(
	SELECT 
		dbo.view_3TreatmentRelevant.CoursePatID, 
		MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
		dbo.view_3TreatmentRelevant.PatSiteID,
		dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
	FROM 
		dbo.view_3TreatmentRelevant
	GROUP BY
		dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
) AS BaseData
PIVOT 
(
	MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN 
	([Check-point-inhib],[Cytokine],[Cytostatic drug],[Targeted])	
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
1)  I want to group by CoursePatId only and get only 1 line for each, but it split up for several ones like the "a".

Below code will take care of this point.

2)  I don't like using static values inside the "PIVOT  FOR  IN" clause. I would prefer dynamic values from a SELECT statement, but that doesn't work. How can I do this?

Done in the below code.

3)  All of it gotta be a view. Is that possible considering 2), or will I have to make a stored procedure?

Since we need to use the dynamic SQL as the names of the pivot columns are dynamic views will not work , we have to use stored procedure.


In this scenario we have to use dynamic SQL so that we can build the string and after that execute using EXEC/EXECUTE or sp_executesql.

I have made your query dynamic. Please try and let us know in case of any issues.

--

/*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]

/*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 
	,SUM(NumberOfTreatments) NumberOfTreatments ' + @MaxVals + '
FROM
(
	SELECT 
		dbo.view_3TreatmentRelevant.CoursePatID, 
		MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
		dbo.view_3TreatmentRelevant.PatSiteID,
		dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
	FROM 
		dbo.view_3TreatmentRelevant
	GROUP BY
		dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, 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 ) 

Open in new window


Read more about exec -
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql

read more about executesql -  
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql

A good read -
http://www.sommarskog.se/dynamic_sql.html
0
 
ullenulleAuthor Commented:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'NumberOfTreatments'.
0
 
Pawan KumarDatabase ExpertCommented:
Updated.

Please try.

--

/*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]

/*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 
	,COUNT(*) NumberOfTreatments ' + @MaxVals + '
FROM
(
	SELECT 
		dbo.view_3TreatmentRelevant.CoursePatID, 
		MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
		dbo.view_3TreatmentRelevant.PatSiteID,
		dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel 
	FROM 
		dbo.view_3TreatmentRelevant
	GROUP BY
		dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, 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 ) 

Open in new window

0
 
ullenulleAuthor Commented:
The error was related to your first response. :-)
0
 
ullenulleAuthor Commented:
This one works from your first post:

SELECT 
     CoursePatId 
	,SUM(NumberOfTreatments) NumberOfTreatments
	,MAX([Check-point-inhib])[Check-point-inhib]
	,MAX([Cytokine]) [Cytokine] 
	,MAX([Cytostatic drug]) [Cytostatic drug]
	,MAX([Targeted]) [Targeted] 
FROM
(
SELECT 
	dbo.view_3TreatmentRelevant.CoursePatID, 
	MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
	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 
	([Check-point-inhib],[Cytokine],[Cytostatic drug],[Targeted])	
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
cool. updated code for you - dynamic.

--

/*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]

/*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 
	,COUNT(*) NumberOfTreatments ' + @MaxVals + '
FROM
(
	SELECT 
	dbo.view_3TreatmentRelevant.CoursePatID, 
	MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
	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 ) 

Open in new window

0
 
ullenulleAuthor Commented:
Additional question to the original comment of yours. I also want to show the FirstTreatStart, but when I add it like this, it says the column name is invalid...:

SELECT 
     CoursePatId
	 ,MIN(FirstTreatStart) FirstTreatStart
	,SUM(NumberOfTreatments) NumberOfTreatments
	,MAX([Check-point-inhib])[Check-point-inhib]
	,MAX([Cytokine]) [Cytokine] 
	,MAX([Cytostatic drug]) [Cytostatic drug]
	,MAX([Targeted]) [Targeted] 
FROM
(
SELECT 
	dbo.view_3TreatmentRelevant.CoursePatID, 
	MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart, 
	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 
	([Check-point-inhib],[Cytokine],[Cytostatic drug],[Targeted])	
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

Open in new window


Can you see why??
0
 
ullenulleAuthor Commented:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'FirstTreatStart'.


It doesn't make sense. It's in the SELECT statement inside the ()...
0
 
Pawan KumarDatabase ExpertCommented:
This is because you are already using that in Pivot. Pls try this-

SELECT 
     CoursePatId
	,MIN(FirstTreatStart1) FirstTreatStart
	,SUM(NumberOfTreatments) NumberOfTreatments
	,MAX([Check-point-inhib])[Check-point-inhib]
	,MAX([Cytokine]) [Cytokine] 
	,MAX([Cytostatic drug]) [Cytostatic drug]
	,MAX([Targeted]) [Targeted] 
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 
	([Check-point-inhib],[Cytokine],[Cytostatic drug],[Targeted])	
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID

Open in new window

0
 
ullenulleAuthor Commented:
Awesome. That worked with the extra column.
0
 
Pawan KumarDatabase ExpertCommented:
Great.

Glad to help as always.
0
 
Pawan KumarDatabase ExpertCommented:
Updated dynamic sql also for you -

--

/*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]

/*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 + '
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 ) 

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
I wrote a couple of Articles a while back that address some of your concerns.

Have a quick read of :  https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
1
 
Pawan KumarDatabase ExpertCommented:
There are N number of articles available on web for dynamic pivoting of data. Please go through this if required.

With example and lot of explanation. First static and then dynamic example is there.

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

Please let me know if you need more explanation.
0
 
Mark WillsTopic AdvisorCommented:
Thanks Pawan for supporting a fellow expert in this place :)
0
 
Pawan KumarDatabase ExpertCommented:
Hi Ulrich, Do you need any more changes ?
0
 
ullenulleAuthor Commented:
Hi Pawan.

Just one question (maybe stupid one): The dynamic code. Is that meant to be a stored procedure, or can I also run it in a query window?
0
 
Mark WillsTopic AdvisorCommented:
either...
0
 
ullenulleAuthor Commented:
Running the code in a query windows gives me this error:

Msg 8156, Level 16, State 1, Line 24
The column 'Targeted' was specified multiple times for 'PivotTable'.

And running it as a stored procedure gives me this:

Msg 156, Level 15, State 1, Procedure proc_q3TreatmentRelevant_Crosstab, Line 9 [Batch Start Line 17]
Incorrect syntax near the keyword 'DECLARE'.
0
 
Pawan KumarDatabase ExpertCommented:
>>Just one question (maybe stupid one): The dynamic code. Is that meant to be a stored procedure, or can I also run it in a query window?
The solution i gave will work in a query as well as in a stored procedure.

could you please provide full code you are running.?
Then I can track the error.
0
 
ullenulleAuthor Commented:
Hi again, Pawan.

Just got back. I used this to create a stored procedure:

USE [mcd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[proc_q3TreatmentRelevant_Crosstab] 

/*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]

/*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 + '
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 ) 

Open in new window


... generating this error:

Msg 156, Level 15, State 1, Procedure proc_q3TreatmentRelevant_Crosstab, Line 5 [Batch Start Line 7]
Incorrect syntax near the keyword 'DECLARE'.

Open in new window


And I used this just to run a query:

--

/*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]

/*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 + '
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 ) 

Open in new window


... creating this error:

Msg 8156, Level 16, State 1, Line 24
The column 'Targeted' was specified multiple times for 'PivotTable'.

Open in new window


Something I missed?

Best regards

Ulrich
0
 
Pawan KumarDatabase ExpertCommented:
For stored procedure., As was missing. Please use below.



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 [dbo].[view_3TreatmentRelevant]

/*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 + '
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 )
0
 
Pawan KumarDatabase ExpertCommented:
The pivot column present twice must be creating the issue. Please check this if it works.

--

/*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]

/*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 + '
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 ) 

Open in new window

0
 
ullenulleAuthor Commented:
Ops... sorry. I missed the "AS" when creating the procedure. :-)

So procedure created fine, but running it or running the code in a query windows gives the same error again:

Msg 8156, Level 16, State 1, Line 26
The column 'Targeted' was specified multiple times for 'PivotTable'.

Why is it referring to "Targeted"?? Nothing is called that in the code?
0
 
Pawan KumarDatabase ExpertCommented:
checking..
0
 
Pawan KumarDatabase ExpertCommented:
Also if possible could you please send few rows from the tables with table schema , so i can test the query against data. Thanks in advance.
0
 
ullenulleAuthor Commented:
I ran the code again and got the same error.

The code is pulling from a view that is pulling from a view that is pulling from 6 joined tables. How about if I save some of the view as a table? Would that be any help?
0
 
ullenulleAuthor Commented:
When done here please look at a related question I just asked. ;-)
0
 
Mark WillsTopic AdvisorCommented:
Not wanting to interfere, but yes, that would probably help a great deal....

Also, uncomment those two PRINT lines (for @vals and @maxvals) so we can see what the Query is trying to do. You dont have to run the whole lot, but if you do, there will be that same error.

I'm fairly sure that's what Pawan would be asking for...
0
 
Ganesh GuruduSenior ConsultantCommented:
can you post the error message and code.?
0
 
Mark WillsTopic AdvisorCommented:
@ganesh, it was posted further up in the thread.

Oh, and the expert running the code at the moment does get annoyed at being usurped.
0
 
Mark WillsTopic AdvisorCommented:
>> Why is it referring to "Targeted"?? Nothing is called that in the code?

Well, yes there is. Looking at your query at the top. There is a column in the Pivot columns [Targeted]

I think I know the problem. When creating @vals and @maxvals we need to make sure they are distinct values.

That part of the code is missing a group by ie :
/*Get names of dynamic columns*/
SELECT 
  @vals = @vals+ '['+ TreatmentDrugHighLevel +']' + ',' 
, @Maxvals = @Maxvals + 'MAX(['+ TreatmentDrugHighLevel +']) ' + '['+ TreatmentDrugHighLevel +']' + ',' 
FROM [dbo].[view_3TreatmentRelevant]
group by TreatmentDrugHighLevel

Open in new window


Now I will keep looking and try to setup some test data (unless you post the rows first)

You are welcome to try adding in that group by in the meantime....
0
 
ullenulleAuthor Commented:
You can actually see the output in my original post above. Only difference after Pawans help is, that there's only 1 row for each CoursePatID.
And there the last column is actually called "Targeted". I just realized that and start to understand a little of the error message.
Error message also some comments above in this thread.
0
 
Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello Author,

Please try. Updated Pawans Code.

--

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)

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

SELECT 
      CoursePatId 
      ,MIN(FirstTreatStart1) FirstTreatStart
      ,SUM(NumberOfTreatments) NumberOfTreatments
      ' + @MaxVals + '
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 )

--

Open in new window

0
 
ullenulleAuthor Commented:
Mark! That was it! The first SELECT fetched all options, and there's multiple duplicates. But with GROUP BY it narrowed down to only one of each! Perfect!  Thank you! :-))
I'm sure Pawan would've found that as well as soon as he's back here. But now you did the work you deserve a little piece of the cake. ;-)
I consider this questions solved now. Awesome! :-))))

The final code for a query window only is:

--

/*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 + '
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 )

Open in new window



And for the Stored Procedure:

USE [mcd]
GO
/****** Object:  StoredProcedure [dbo].[proc_q3TreatmentRelevant_Crosstab]    Script Date: 12/15/2017 10:43:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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 [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 + '
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 )

Open in new window


Aaawwwwwesome! :-)
0
 
ullenulleAuthor Commented:
This is part of the output:

pivot_output.JPG
Can any of you tell me what the "Return Value" with 0 means?? How did that show up? It's when I run the SP.
0
 
Mark WillsTopic AdvisorCommented:
Okay, while there is still some tidy up, this now seems to work on a small data sample :

/*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 + '
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 )

Open in new window


That unique columns / group by is explained in my article : Have a quick read of :  https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html

Hope this is all working for you now.

Cheers,
Mark Wills
0
 
Mark WillsTopic AdvisorCommented:
Great stuff. So very happy it is working for you. More importantly,... So very happy that you are so very happy :)

Return code is the procedure finishing, we can tidy up in your other question.

Cheers,
Mark Wills
0
 
ullenulleAuthor Commented:
Thank you all again. I hope you feel ok with the way i distributed the points? :-)
0
 
Mark WillsTopic AdvisorCommented:
Well, I do think you could have selected your own in #a42405270

After all that was the first one working properly.

Other than that, the best trail is :

Pawan #a42405002
Pawan #a42405029  (the current green one, but missing a column)
Mark   #a42405244 (the 'aha' moment)
Mark   #a42405276 The full solution

Think it helps others identify the posts that made a difference without having to wade through a fairly long thread. :)

Cheers,
Mark Wills
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.