set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE Procedure [dbo].[DynamicForms_ExportFormResults_ForTraining] @ModuleID int AS Declare curBubba Cursor FOR SELECT Question, DynamicQuestionID FROM dynamicforms_question where QuestionType <> 'HTML' AND QuestionType <> 'HR' AND InActive=0 AND ModuleID = 1113 AND Question <> 'Postal Code' ORDER BY SortOrder FOR READ ONLY; Declare @Question VarChar(500); Declare @DynamicQuestionID uniqueidentifier; Declare @SQLToUse VarChar(8000); Declare @SQLToUseJoin VarChar(8000); Declare @SQLToExecute Varchar(8000); Declare @Alias VarChar(10); Declare @intCounter Int; set @Question = ''; set @SQLToUse = ''; set @intCounter = 0; set @Alias = ''; set @SQLToUseJoin = ''; OPEN curBubba; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; WHILE @@FETCH_STATUS = 0 BEGIN set @intCounter = @intCounter + 1; set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']'; if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'SELECT IsNull(' + @Alias + '.response,''N/A'') as [' + @Question + '] ' set @SQLToUseJoin = ' From ( SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName from [DynamicForms_QuestionResponse] As Y inner join DynamicForms_Question as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID) Left Outer Join Users B on B.UserID = Y.UserID where Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + ' ) As A Left Outer join DynamicForms_QuestionResponse as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END ELSE BEGIN set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + @Question + '] ' set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join DynamicForms_QuestionResponse as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) ' END; FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID; END CLOSE curBubba; DEALLOCATE curBubba; Set @SQLToExecute = Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) --set @SQLToExecute = @SQLToUse + @SQLToUseJoin --print @SQLToExecute EXEC(@SQLToExecute);