The Force 5 Connection

CREATE PROCEDURE [dbo].[Survey_Answers]
AS
BEGIN
	SET NOCOUNT ON;
 
	-- Declare variables
	DECLARE @QuestionID VARCHAR(20), @SQL VARCHAR(MAX)
 
	-- Create empty temporary table with id column
	CREATE TABLE #tempTable (SurveyID INT NULL)
 
	---- Insert Columns into pivot table ----
	-- Declare cursor to loop through table
	DECLARE curQuestions CURSOR FOR
	SELECT     QuestionID
	FROM         Survey_Questions
 
	OPEN curQuestions
 
	FETCH NEXT FROM curQuestions INTO @QuestionID
	WHILE @@FETCH_STATUS=0
	BEGIN
		-- Defines each column
		SET @SQL = 'ALTER TABLE #tempTable ADD ' + @QuestionID + ' varchar(1024) NULL'
		-- Executes the command which creates the column in the temp table
		EXEC(@SQL)
		FETCH NEXT FROM curQuestions INTO @QuestionID
	END
 
	-- Clean up cursor
	CLOSE curQuestions
	DEALLOCATE curQuestions
	---- End of Insert Columns section ----
 
	---- Insert id values into pivot table ----
	-- Create rows in temp table using IDs from Survey table
	INSERT INTO [#tempTable] (SurveyID)
		SELECT     SurveyID
		FROM         Survey
 
	---- Insert data into pivot table ----
	-- Loop through each row in Survey_Answers
	-- Update values in pivot table
 
	-- Declare variables
	DECLARE @SurveyID INT, @QuestionID2 VARCHAR(20), @Answer VARCHAR(1024), @CurrentSurveyID INT
 
	-- Initialize variables
	SET @CurrentSurveyID = -1
	SET @SQL = ''
 
	-- Declare cursor to loop through table
	DECLARE curAnswers CURSOR FOR
	SELECT     Survey_Answers.SurveyID, Survey_Answers.QuestionID, Survey_Answers.Answer
	FROM         Survey_Answers INNER JOIN
						  Survey ON Survey_Answers.SurveyID = Survey.SurveyID
	ORDER BY Survey_Answers.SurveyID
 
	OPEN curAnswers
 
	FETCH NEXT FROM curAnswers INTO @SurveyID, @QuestionID2, @Answer
	WHILE @@FETCH_STATUS=0
	BEGIN
		IF @CurrentSurveyId<>@SurveyId
			BEGIN
				-- This will run at the end of a set of questions related to one survey
				-- And initializes variables for next set of questions
				IF @sql<>''
					BEGIN
						SET @SQL = STUFF(@SQL, LEN(@SQL), 1, ' WHERE (SurveyID = ' + CONVERT(VARCHAR, @CurrentSurveyId) + ');')
						EXEC(@SQL)
					END
				SET @SQL = 'UPDATE [#tempTable] SET'
				SET @CurrentSurveyId = @SurveyId
			END
 
		-- Update values in pivot table
		SET @SQL = @SQL + ' ' + @QuestionId2 + ' = ''' + @Answer + ''','
		FETCH NEXT FROM curAnswers INTO @SurveyID, @QuestionID2, @Answer
 
		-- This section takes care of the last row since it will not go through the IF @sql<>'' code above. Uses same code as that section
		IF @@FETCH_STATUS = -1
			BEGIN
				SET @SQL = STUFF(@SQL, LEN(@SQL), 1, ' WHERE (SurveyID = ' + CONVERT(VARCHAR, @CurrentSurveyId) + ');')
				EXEC(@SQL)
			END
	END
 
	-- Clean up answers cursor
	CLOSE curAnswers
	DEALLOCATE curAnswers
 
	-- Select values from created table
	SELECT     [#tempTable].*, Survey.DateCreated
	FROM         Survey INNER JOIN
		[#tempTable] ON Survey.SurveyID = [#tempTable].SurveyID
	ORDER BY Survey.SurveyID 
 
	-- Clean up the pivot table
	DROP TABLE #tempTable
END