Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL 2005 Stored Procedure Help!

Posted on 2011-03-24
2
238 Views
Last Modified: 2012-05-11
I have the following Stored Procedure that selects Survey results based on 3 Parameters @EventID, @FromDate and @ToDate. Each Survey online has 5 questions the answers to those questions are stored in  the AnswerText field the 5th Question with QuestionID = 5 has a comments field that is stored in AnswerText2.

What I want is a single line for each user that has the following info.

Title, ReportDate, LastName, FirstName, MiddleInitial, Designation, Address1, Address2, City, State, Zip, SurveyDate, Credit, AnswerText where QuestionID =1, AnswerText where QuestionID =2, AnswerText where QuestionID =3, AnswerText where QuestionID =4,  AnswerText where QuestionID =5,  AnswerText2 where QuestionID =5

So I would have a single line for each user with there answers stored in there own column.

What I get now is 5 rows for each user with all their personal information like name and address duplicated and all the answers are in the same column.
USE [EXPORTHpixPROD]
GO
/****** Object:  StoredProcedure [dbo].[spGetMonthlyEvaluationByEventID]    Script Date: 03/24/2011 07:25:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Michael Burk
-- Create date: 03232011
-- Description:	Report that selects Evaluations by EventID and Month
-- =============================================
ALTER PROCEDURE [dbo].[spGetMonthlyEvaluationByEventID] 
	-- Add the parameters for the stored procedure here
	(@EventID AS BIGINT,
	 @FromDate AS VARCHAR(10),
	 @ToDate AS VARCHAR(10))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT    
						CME_Parsed_Survey_XML.SurveyID
					   ,CME_Parsed_Survey_Results_XML.SurveyID
					   ,CONVERT(VARCHAR (10),SurveyDate,101) AS SurveyDate
					   ,CME_Parsed_Survey_XML.EventID
					   ,CME_Parsed_Survey_XML.LicenseNumber
					   ,CME_Parsed_Survey_Results_XML.QuestionID
					   ,CME_Parsed_Survey_Results_XML.QuestionText
					   ,CME_Parsed_Survey_Results_XML.AnswerTextID
					   ,CME_Parsed_Survey_Results_XML.AnswerText
					   ,CME_Parsed_Survey_Results_XML.AnswerText2
					   ,CME_Parsed_Registration_XML.Designation
					   ,CME_Parsed_Registration_XML.FirstName
					   ,CME_Parsed_Registration_XML.MiddleInitial
					   ,CME_Parsed_Registration_XML.LastName
					   ,CME_Parsed_Registration_XML.Title AS Suffix
					   ,CME_Parsed_Registration_XML.Address1
					   ,CME_Parsed_Registration_XML.Address2
					   ,CME_Parsed_Registration_XML.City
					   ,CME_Parsed_Registration_XML.State
					   ,CME_Parsed_Registration_XML.ZipCode
					   ,CME_Courses.Title AS Title
					   ,CME_Courses.Credit
					   ,@ToDate AS ReportDate					  
FROM        CME_Parsed_Survey_XML INNER JOIN
			CME_Parsed_Survey_Results_XML ON CME_Parsed_Survey_XML.SurveyID = CME_Parsed_Survey_Results_XML.SurveyID
			INNER JOIN
			CME_Parsed_Registration_XML ON CME_Parsed_Survey_XML.LicenseNumber = CME_Parsed_Registration_XML.MedicalLicenseNumber
			INNER JOIN
			CME_Courses ON CME_Parsed_Survey_XML.EventID = CME_Courses.EventID
WHERE SurveyDate BETWEEN @FromDate AND @ToDate AND dbo.CME_Courses.EventID = @EventID




END

Open in new window

0
Comment
Question by:mburk1968
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35209726
try this.
USE [EXPORTHpixPROD] 

GO 

/****** Object:  StoredProcedure [dbo].[spGetMonthlyEvaluationByEventID]    Script Date: 03/24/2011 07:25:14 ******/ 
SET ANSI_NULLS  ON 

GO 

SET QUOTED_IDENTIFIER  ON 

GO 

-- =============================================  
-- Author:    Michael Burk  
-- Create date: 03232011  
-- Description:  Report that selects Evaluations by EventID and Month  
-- ============================================= 
ALTER PROCEDURE [dbo].[SPGETMONTHLYEVALUATIONBYEVENTID]-- Add the parameters for the stored procedure here  
( 
                @EventID AS BIGINT, 
               @FromDate AS VARCHAR(10), 
                 @ToDate AS VARCHAR(10)) 
AS 
  BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements. 
    SET NOCOUNT  ON; 
     
    -- Insert statements for procedure here 
    ;WITH cte 
         AS (SELECT ps.SurveyID, 
                    psr.SurveyID                        AS psr_SurveyID, 
                    CONVERT(VARCHAR(10),SurveyDate,101) AS SurveyDate, 
                    ps.EventID, 
                    ps.LicenseNumber, 
                    psr.QuestionID, 
                    psr.QuestionText, 
                    psr.AnswerTextID, 
                    psr.AnswerText, 
                    psr.AnswerText2, 
                    pr.Designation, 
                    pr.FirstName, 
                    pr.MiddleInitial, 
                    pr.LastName, 
                    pr.Title                            AS Suffix, 
                    pr.Address1, 
                    pr.Address2, 
                    pr.City, 
                    pr.State, 
                    pr.ZipCode, 
                    c.Title                             AS Title, 
                    c.Credit, 
                    @ToDate                             AS ReportDate 
               FROM CME_Parsed_Survey_XML ps 
                    INNER JOIN CME_Parsed_Survey_Results_XML psr 
                      ON ps.SurveyID = psr.SurveyID 
                    INNER JOIN CME_Parsed_Registration_XML pr 
                      ON ps.LicenseNumber = pr.MedicalLicenseNumber 
                    INNER JOIN CME_Courses c 
                      ON ps.EventID = c.EventID 
              WHERE SurveyDate BETWEEN @FromDate AND @ToDate 
                    AND c.EventID = @EventID) 
      SELECT SurveyID, 
             psr_SurveyID, 
             SurveyDate, 
             EventID, 
             LicenseNumber, 
             1        AS QuestionID1, 
             MAX(CASE 
                   WHEN QuestionID = 1 THEN QuestionText 
                 END) AS QuestionText1, 
             MAX(CASE 
                   WHEN QuestionID = 1 THEN AnswerText 
                 END) AS AnswerText1, 
             2        AS QuestionID2, 
             MAX(CASE 
                   WHEN QuestionID = 2 THEN QuestionText 
                 END) AS QuestionText2, 
             MAX(CASE 
                   WHEN QuestionID = 2 THEN AnswerText 
                 END) AS AnswerText2, 
             3        AS QuestionID3, 
             MAX(CASE 
                   WHEN QuestionID = 3 THEN QuestionText 
                 END) AS QuestionText3, 
             MAX(CASE 
                   WHEN QuestionID = 3 THEN AnswerText 
                 END) AS AnswerText3, 
             4        AS QuestionID4, 
             MAX(CASE 
                   WHEN QuestionID = 4 THEN QuestionText 
                 END) AS QuestionText4, 
             MAX(CASE 
                   WHEN QuestionID = 4 THEN AnswerText 
                 END) AS AnswerText4, 
             5        AS QuestionID5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN QuestionText 
                 END) AS QuestionText5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN AnswerText 
                 END) AS AnswerText5, 
             MAX(CASE 
                   WHEN QuestionID = 5 THEN AnswerText2 
                 END) AS AnswerText5_2, 
             Designation, 
             FirstName, 
             MiddleInitial, 
             LastName, 
             Suffix, 
             Address1, 
             Address2, 
             City, 
             [State], 
             ZipCode, 
             Title, 
             Credit, 
             ReportDate 
        FROM cte 
    GROUP BY SurveyID, 
             psr_SurveyID, 
             SurveyDate, 
             EventID, 
             LicenseNumber, 
             Designation, 
             FirstName, 
             MiddleInitial, 
             LastName, 
             Suffix, 
             Address1, 
             Address2, 
             City, 
             [State], 
             ZipCode, 
             Title, 
             Credit, 
             ReportDate 
  END

Open in new window

0
 

Author Closing Comment

by:mburk1968
ID: 35209747
Perfect!

Thank You
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question