SQL Case Study – Convert data rows to columns

We recently had a project that involved putting together a survey. This survey was comprised of almost 150 questions. As we brainstormed the best way to construct the data tables to store this information, the thought of a table with 150 columns made us cringe. Time constraints also called for something we could put together relatively quickly. We decided to create a table that stored each question as a row of data. Then we made a table that referenced the primary key of the question table along with the user’s answer to that question. So instead of having a table with 150 columns, we have one table with 150 rows and another table that stores a data row for each question answered on the survey. Now if a question needs to be added or removed from the survey all that needs to happen is add or remove a row from the questions table.

It also made collecting the survey data through a ASP.NET Web Site a lot easier, but that can be a future blog topic.

All of that was a setup for displaying the following solution that we created. In order to display the data correctly for reporting purposes we needed to be able to transform the 150 rows of data in the questions table into a table with that data as column names. In simpler terms, we needed to convert a set of data rows in table columns in a temporary table. Then we needed to be able to populate that table with the data from the answers table.

Here is the solution we came up with using the power of a stored procedures in Microsoft SQL Server.

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