July 29, 2013 at 9:42 am
Hi All,
It is fairly common where we work to have a questionnaire output in the form of two tables, one that contains a set of questions and another that contains a matching set of answers, along with a UserId and Timestamp. In order to display these nicely, it is useful for us to display these two as one table, where the questions become the column names and the answers become the values, all in one row per questionnaire. I have written some code which does this fine (see the example below, where the output tables correspond to #Questions and #Answers), but it seems to me that this is a lot of code, a lot of dynamic SQL and generally pretty involved looking.
Is there a simpler way to do the same thing? Do you have any ideas of things that MIGHT make it all simpler and (maybe) quicker?
Cheers,
Mark
CREATE TABLE #Questions
(
s01q01VARCHAR(50)
,s01q02VARCHAR(50)
,s02q01VARCHAR(50)
,s02q02VARCHAR(50)
,s02q03VARCHAR(50)
)
CREATE TABLE #Answers
(
CompletingUserVARCHAR(50)
,CompletingTimeVARCHAR(50)
,s01q01VARCHAR(50)
,s01q02VARCHAR(50)
,s02q01VARCHAR(50)
,s02q02VARCHAR(50)
,s02q03VARCHAR(50)
)
INSERT #Questions
SELECT'Question One','Question Two','Question One B','Question Two B','Question Three B'
INSERT #Answers
SELECT'Darth Vader',GETDATE(),'definitiely 1','There are Two','I started a new section','£22.00',NULL
---------------------------------------------------------------------------------------------------------------------------------------------
-- Enter an empty string so that this is no longer NULL, which means we can easily add to it
DECLARE @ColumnList VARCHAR(MAX) = ''
DECLARE @SQL VARCHAR(MAX)
CREATE TABLE #AnswerList (Label VARCHAR(10), Wording VARCHAR(MAX))
CREATE TABLE #QuestionList (Label VARCHAR(10), Wording VARCHAR(MAX))
-- The columnlist here is our input list of columns to Pivot about
SELECT @ColumnList = @ColumnList + ',' + COLUMN_NAME
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#Questions%'
-- lose the extaneous comma from our concatenated list of columns
SELECT @ColumnList = SUBSTRING(@ColumnList,2,LEN(@ColumnList))
-- make our dynamic SQL with the column list in it and any changes will be incorporated into our query
SET @SQL =
-- First make the list of answers, with the labels in one column and the wording in the other
'INSERT #AnswerList
SELECT Label,Answer FROM
(SELECT CompletingUser, CompletingTime,' + @ColumnList + ' FROM #Answers) AS PVT
UNPIVOT (Answer FOR Label IN (' + @ColumnList +')) AS UnPvt
' +
-- Now make a similar list of questions, with the same values in the labels row as the answers (this is always true)
'INSERT #QuestionList
SELECT Label,Question FROM
(SELECT ' + @ColumnList + ' FROM #Questions) AS PVT
UNPIVOT (Question FOR Label IN (' + @ColumnList +')) AS UnPvt'
EXEC (@SQL)
-- Make a new column list - this is now the output column list, taken as the list of the label of al the questions
SET @ColumnList = ''
-- Put it all nicely in square brackets
SELECT @ColumnList = @ColumnList + ',[' + Wording + ']'
FROM #QuestionList
-- Lose the comma off the front
SET @ColumnList = SUBSTRING(@ColumnList,2,LEN(@ColumnList))
-- Now get the one-off info from the Answers and all the dynamic questions and answers
SET @SQL =
'SELECT
(SELECT CompletingUser FROM #Answers) AS CompletingUser
,(SELECT CompletingTime FROM #Answers) AS CompletingTime
,*
FROM (SELECT Q.Wording AS Question, A.Wording AS Answer
FROM #QuestionListAS Q
' +
-- Left outer join as some questions may be unanswered, so having NULL values
'LEFT OUTER JOIN #AnswerListAS AON Q.Label = A.Label) AS QA
' +
-- it doesn't know that we have only one row, so we have to put a MAX in, but it makes no different because we DO have only one row.
'PIVOT(MAX([Answer]) FOR Question IN ('+ @ColumnList +')) as Pvt'
EXEC (@SQL)
DROP TABLE #Answers
DROP TABLE #Questions
DROP TABLE #AnswerList
DROP TABLE #QuestionList
All suggestions most welcome!
July 30, 2013 at 7:31 am
Hi,
Rather than storing the questions as columns, store them as rows. ie.
create table Questions(
QuestionID int,
Question varchar(200)
)
Also, makes it a lot simpler if you need to add a new question (because user's like to do that sort of thing!)
Store the answers in a similar way
create table Answers(
AnswerID int,
QuestionID int,
Answer varchar(50),
UserID int,
DateAnswered datetime
)
By adding a 'QuestionaireID' to the Question table, you can also store more than one questionnaire in the same table, thus avoiding having to create new tables and procedures, when the users find another questionnaire that needs automating.
July 30, 2013 at 9:23 am
Hi,
thanks for the answer - unfortunately I had hoped this could be avoided, as it means that I'll have to spend a very long time changing a lot of things and asking our software providers to change things as well (we don't really get a choice about the format they supply in, and we use the separate tables in SSRS, so one row results is good for that.).
I guess we just have to say that the complexity is a necessary evil until we can get things changed in a larger way here.
Thanks again, and to all that have read this!
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply