Stored procedure issue...

I had this question after viewing Syntax error in Stored Procedure....

I am working on converting another MS Access Crosstab/Pivot query to a stored procedure in SQL Server. Here's the SQL code from the Access query:

TRANSFORM Count(q3Treatment.TreatID) AS CountOfTreatID
SELECT q2Course.CoursePatID, q2Course.SiteCity, q3Treatment.CoursePatID, Count(q3Treatment.TreatID) AS [Total Of TreatID]
FROM q2Course LEFT JOIN q3Treatment ON q2Course.CoursePatID = q3Treatment.CoursePatID
GROUP BY q2Course.CoursePatID, q2Course.SiteCity, q3Treatment.CoursePatID
PIVOT Format([TreatStart],"yyyy");

Open in new window


I tried to edit the SP in the question above, but I fail to do it right. Heeeellppp? :-)

Best regards

Ulrich
ullenulleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello ullenulle

Getting Error in below ? Appreciate if you would provide us the error detail.

USE [mcd]
GO
/****** Object:  StoredProcedure [dbo].[proc_q3TreatmentRelevant_Crosstab]    Script Date: 12/18/2017 10:47:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_q3TreatmentRelevant_PD1_inhib]
AS

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

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

/*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.[TreatDrugText]
        FROM
            dbo.view_3TreatmentRelevant
      WHERE [TreatDrugText]=''pembrolizumab'' OR [TreatDrugText]=''nivolumab''  
      GROUP BY
            dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.[TreatDrugText]
) AS BaseData
PIVOT
(
      MIN(BaseData.FirstTreatStart) FOR [TreatDrugText] IN (' + @Vals + ')      
) AS PivotTable
GROUP BY CoursePatId
ORDER BY CoursePatID'

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

Vaibhav
0
Mark WillsTopic AdvisorCommented:
Ummm, not obvious what you are trying to change in the other procedure. maybe using it as a template ?

from what I can tell, it is an entirely different Access query (mind you I didnt really examine the previous one until recently)...

Is this a change to the SP or a new one ? Methinks a new one...

If possible, can you show me a sample of your Access transform query ? Data ?
0
ullenulleAuthor Commented:
Hi Mark.

Yes, it's another SP and I tried to use the other one as a template (as I did with others successfully), but this one is tricking me... probably because I must group by DATEPART(yyyy, TreatStart). I'll add an example of the result and the setup from Access:

Access setup...
Access output to do in SQL Server as well.
Best regards

Ulrich
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark WillsTopic AdvisorCommented:
Okay,

If I do it long hand, do you think that would give you the basic needs, we can always go through making it a dynamic query.

But, considering you have done a couple now, I thought that you might enjoy the challenge of putting into a SP and dynamic Query :)

I kept as close as possible to the layout of your Access script at the top so you have a conversion path for next time.

A few assumptions...
1) naming conventions the prior questions replaced the prefix 'Q3' with 'view_3' so I followed suit.
2) didnt know where treatStart comes from. So labelled it as view_2Course and pretty sure it is wrong
3) you have a left join and if TreatStart is in view_3 then it should be inner join - or - need to check for NULL (ie is not null)
4) because SiteCity is in view2, and we group by, then each  coursepatid can only belong to one SiteCity
5) I did the same/similar group by for the outer query, mainly for consistency with prior questions.
6) you had view_3Treatment.CoursePatID in the select list, but , would be the same as view_2
7) didnt have TreatStart in the select list, so added it in.
8) it is a bit of a guess, I dont have tables or data, so you might get some errors.... so run this first for limited data before you start on the proc + dynamic SQL


Anyway, here is the manual version....
select  CoursePatID, SiteCity, sum(treatcounter) AS [Total Of TreatID],  isnull(sum([2015]),0) as [2015], isnull(sum([2016]),0) as [2016], isnull(sum([2017]),0) as [2017]  from
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_2Course.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_2Course.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in ([2015],[2016],[2017])
) p
group by CoursePatID, SiteCity
order by 1,2

Open in new window

Let me know how you are going with it, it should work but I had to make assumptions...

read my article for some tips : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html


Have fun (?)
0
ullenulleAuthor Commented:
Hi Mark.

Thank you for your response.  Small edit to the query... the "TreatStart" was from the view_3Treatment. :-) So now the query looks fine. I'll try to add the dynamic stuff now. Be back in a few with update.

select  CoursePatID, SiteCity, sum(treatcounter) AS [Total Of TreatID],  isnull(sum([2015]),0) as [2015], isnull(sum([2016]),0) as [2016], isnull(sum([2017]),0) as [2017]  from
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in ([2015],[2016],[2017])
) p
group by CoursePatID, SiteCity
order by 1,2

Open in new window


Best regards

Ulrich
0
Mark WillsTopic AdvisorCommented:
>> the "TreatStart" was from the view_3Treatment.

*laughing* I knew I had it wrong - changed it back and forth a few times...

Yuo'll notice that the inner query is pretty much the same as in Access.

The selection list to display (ie the first select) is different - thats where you would normally have the 'transform' line and SQL has select.

And of course the last part is the Pivot statement

So, there is a lot of equivalences (or similarities if you squint hard enough) . Sure syntax is quite a bit different when you go to write code, but the single biggest difference is the pain of having to explicitly code for the new columns... And that is why we resort to dynamic SQL.
0
ullenulleAuthor Commented:
The  
isnull(sum([2015]),0) as [2015], isnull(sum([2016]),0) as [2016], isnull(sum([2017]),0) as [2017] 

Open in new window

... how do I implement the dynamic into that part of the show?
0
ullenulleAuthor Commented:
My dynamic columns is like this one:

SELECT 
MAX(DATEPART(yyyy, [TreatStart]))
FROM [dbo].[view_3Treatment]
GROUP BY DATEPART(yyyy, [TreatStart])
ORDER BY DATEPART(yyyy, [TreatStart])

Open in new window


And using the concept from the other SP's should be something like this?

SELECT 
  @vals = @vals+ 'MAX(DATEPART(yyy, ['+ TreatmentDrugHighLevel +']))' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY DATEPART(yyyy, [TreatStart])
ORDER BY DATEPART(yyyy, [TreatStart])

Open in new window

0
ullenulleAuthor Commented:
Ok... I'm up to this now:

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

/*Get names of dynamic columns*/
SELECT 
  @vals = @vals+ 'MAX(DATEPART(yyy, ['+ TreatStart +']))' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY DATEPART(yyyy, [TreatStart])
ORDER BY DATEPART(yyyy, [TreatStart])

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

--PRINT @vals

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

select  CoursePatID, SiteCity, sum(treatcounter) AS [Total Of TreatID],  isnull(sum([2015]),0) as [2015], isnull(sum([2016]),0) as [2016], isnull(sum([2017]),0) as [2017]  from
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in (' + @Vals + ')
) p
group by CoursePatID, SiteCity
order by 1,2'

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

Open in new window


... and I get this error:

Msg 8120, Level 16, State 1, Line 6
Column 'dbo.view_3Treatment.TreatStart' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Open in new window

0
Mark WillsTopic AdvisorCommented:
The same as before, just a bit more "literal" to accommodate.

 DECLARE @vals AS VARCHAR(MAX) = ''
 DECLARE @Maxvals AS VARCHAR(MAX) = ''

 SELECT 
  @vals = @vals+ '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
, @Maxvals = @Maxvals + 'ISNULL(SUM(['+ datename(year,view_3Treatment.treatstart) +']),0) ' + '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY datename(year,view_3Treatment.treatstart)

print @vals
print @maxvals

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
those pesky single quotes need to be in the right spot.... double check how you build @vals and @maxvals....

The column is datename(year,view_3Treatment.treatstart)
0
ullenulleAuthor Commented:
I can't use DATEPART? I noticed you use DATENAME that returns an integer?
0
ullenulleAuthor Commented:
Aha... getting better and better:

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

/*Get names of dynamic columns*/
 SELECT 
  @vals = @vals+ '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
, @Maxvals = @Maxvals + 'ISNULL(SUM(['+ datename(year,view_3Treatment.treatstart) +']),0) ' + '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY datename(year,view_3Treatment.treatstart)

/*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, SiteCity, sum(treatcounter) AS [Total Of TreatID],  isnull(sum([2015]),0) as [2015], isnull(sum([2016]),0) as [2016], isnull(sum([2017]),0) as [2017]  from
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in (' + @Vals + ')
) p
group by CoursePatID, SiteCity
order by 1,2'

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

Open in new window


Last thing is to trick the dynamic into the isnull(sum([2015]),0) as [2015]  etc... that I'm still trying to understand for real... feeling doohh... :-(
0
Mark WillsTopic AdvisorCommented:
datename(year,getdate()) returns a string
datepart(year,getdate()) returns a number

adding strings concatenates  e.g '1'+'1' => '11'
adding numbers aggregates  e.g  1+1 => 2
0
ullenulleAuthor Commented:
HA! Got it:

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

/*Get names of dynamic columns*/
 SELECT 
  @vals = @vals+ '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
, @Maxvals = @Maxvals + 'ISNULL(SUM(['+ datename(year,view_3Treatment.treatstart) +']),0) ' + '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY datename(year,view_3Treatment.treatstart)

/*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, SiteCity, sum(treatcounter) AS [Total Of TreatID] ' + @MaxVals + ' FROM
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in (' + @Vals + ')
) p
group by CoursePatID, SiteCity
order by 1,2'

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

Open in new window

0
ullenulleAuthor Commented:
And a litte order tweak... :-)  
/*Declare Variables*/
 DECLARE @vals AS VARCHAR(MAX) = ''
 DECLARE @Maxvals AS VARCHAR(MAX) = ''

/*Get names of dynamic columns*/
 SELECT 
  @vals = @vals+ '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
, @Maxvals = @Maxvals + 'ISNULL(SUM(['+ datename(year,view_3Treatment.treatstart) +']),0) ' + '['+ datename(year,view_3Treatment.treatstart) +']' + ',' 
FROM [dbo].[view_3Treatment]
GROUP BY datename(year,view_3Treatment.treatstart)
ORDER BY datename(year,view_3Treatment.treatstart)

/*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, SiteCity, sum(treatcounter) AS [Total Of TreatID] ' + @MaxVals + ' FROM
(
 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)
) d
PIVOT
(
 sum([treatcount]) for treatstart in (' + @Vals + ')
) p
group by CoursePatID, SiteCity
order by 1,2'

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

Open in new window

0
ullenulleAuthor Commented:
I need to do a million of these before I will feel comfortable about building them... but one more edition to my experience. :-)
Thank you big time!  :-))
0
Mark WillsTopic AdvisorCommented:
A pleasure...

You do realise that this is never as easy as one would like. So very easy to get it wrong. That's why you need to include a lot of PRINT statements to help debug and actually visualise the results.

Always start with little steps, run, check results, add the next bit. Do it, Try it, Fix it (or wash rinse repeat)

cheers,
Mark
0
Mark WillsTopic AdvisorCommented:
And another tip, use table Alias's

e.g.

 SELECT view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart) treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course LEFT JOIN view_3Treatment ON view_2Course.CoursePatID = view_3Treatment.CoursePatID
 GROUP BY view_2Course.CoursePatID, view_2Course.SiteCity, datename(year,view_3Treatment.treatstart)

Open in new window

Can also be written as
 SELECT C.CoursePatID, C.SiteCity, datename(year,T.treatstart) as treatstart, Count(*) as treatcount ,Count(*) as treatcounter
 FROM view_2Course C 
 LEFT JOIN view_3Treatment T ON C.CoursePatID = T.CoursePatID
 GROUP BY C.CoursePatID, C.SiteCity, datename(year,T.treatstart)

Open in new window

Or, can also do   FROM view_2Course AS C  --  similar to the way we name a column
0
Mark WillsTopic AdvisorCommented:
Havent heard from you and curiosity has gotten the best of me....

So, how are you going ?

And another tip with string concatenation ... you can use XML
-- using XML, we can auto-concatenate data e.g.

select distinct datename(year,view_3Treatment.treatstart) FROM [dbo].[view_3Treatment] for xml path('')

-- so we can use that to our advantage in building concatenated strings from a data source

declare @col as varchar(max)
 
SET @col = stuff((select distinct ',['+ datename(year,view_3Treatment.treatstart) +']' FROM [dbo].[view_3Treatment] for xml path('')),1,1,'')

print @col

Open in new window

0
ullenulleAuthor Commented:
Thank you again... also for your final comment with hints. :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.