CREATE FUNCTION [dbo].[getTotalResponsesBySurveyId_FTable]
(		
	@surveyId nvarchar(20) 	
)
RETURNS TABLE 
AS
RETURN 
(
	 Select dbo.RegExReplace(
	(Select ISNULL(ST1.QuestionResponse, '') + ','  AS [text()]
        From [dbo].[SurveyAnswers] ST1
        Where ST1.SysParticipantID = ST2.SysParticipantID  			
        	AND ST1.SurveyID = @surveyId
        	-- AND ST1.PageNumber <= 7
        Order by ST1.QuestionNumber
        For XML PATH ('')
    ), 'TRUE', 'FALSE') + ',' + CHAR(10) [Response]
 From (select distinct SysParticipantID from [dbo].[SurveyAnswers] Where SurveyID=@surveyId) ST2 
)

select * from dbo.getTotalResponsesBySurveyId_FTable('Test1')

Select ISNULL(ST1.QuestionResponse, '') + ','   AS [text()]
        From [dbo].[SurveyAnswers] ST1        
        Where ST1.SurveyID = 'test1'        	
			 AND ST1.PageNumber <= 7
			 AND ST1.SysParticipantID = 90						
        Order by ST1.QuestionNumber
        For XML PATH ('')

Declare @surveyData Nvarchar(max)
Declare @surveyId Nvarchar(10)
Declare @participantId int

Set @surveyId = 'Test1'
Set @participantId = 90

-- get current modified text information from database
SET @surveyData = (SELECT dbo.RegExReplace(
	(Select ISNULL(ST1.QuestionResponse, '') + ','  AS [text()]
        From [dbo].[SurveyAnswers] ST1
        Where ST1.SysParticipantID = ST2.SysParticipantID  			
        	AND ST1.SurveyID = @surveyId        	
        Order by ST1.QuestionNumber
        For XML PATH ('')
    ), 'TRUE', 'FALSE') + ',' + CHAR(10) [Response]
 From (select distinct SysParticipantID from [dbo].[SurveyAnswers] Where SurveyID=@surveyId AND SysParticipantID = @participantId) ST2 )
  
 Print @surveyData

Test1 User,Test Collections/Customer Service,Test Services,Indianapolis,IN,USA,5,No,,Female,1900,White,Test Collections,1,Yes,Do not know,Collections,Test and Dark Facility,5,No,,No,,No,,Unsure,Yes,Yes,04,03,No,Yes,02,03,Yes,Yes,04,04,Yes,Yes,04,02,No,Yes,03,02,No,No,04,05,Yes,Yes,04,04,Yes,Yes,05,05,Yes,Yes,05,05,Yes,Yes,05,05,No,No,02,03,Yes,Yes,04,03,Yes,Yes,05,05,Yes,Yes,05,05,No,Yes,03,02,No,No,02,02,Yes,Yes,03,03,No,No,03,03,Yes,No,03,03,No,No,04,04,Yes,Yes,05,05,Yes,No,04,04,Yes,Yes,04,05,Yes,Yes,04,04,Yes,Yes,04,04,Yes,No,04,03,Yes,Yes,05,05,No,Yes,02,02,No,Yes,04,04,No,Yes,04,04,Yes,Yes,05,05,No,Yes,04,04,No,Yes,03,03,No,Yes,03,03,No,Yes,03,02,Yes,Yes,05,05,No,No,02,02,Yes,Yes,05,05,Yes,Yes,05,05,066-R0,

cкачать