NorthStarDA
IS-IT--Management
hi,
i have a database of contacts and surveys, my software is similar to surveymonkey.com where i let people create and use surveys. i need to allow users to export the survey data but since this system is entirely automated, i need to query the survey data without hard-coding any survey columns.
the relevant survey data is like this:
SURVEYS
-surveyID (PK)
-surveyName
PAGES
-pageID (PK)
-surveyID (FK)
-pageOrder
QUESTIONS
-questionID (PK)
-pageID (FK)
-questionOrder
-questionText
QUESTIONOPTIONS
-questionOptionID (PK)
-questionID (FK)
-questionOptionText
-questionOptionOrder
QUESTIONANSWERS (1 question can have multiple answers from the same contact, but not the same option)
-questionAnswerID (PK)
-questionID (FK)
-contactID (FK)
-questionOptionID (FK)
I need the query output to be each question in it's own column, and if there were multiple responses to that question (questionanswers) that they be in a CSV format.
-----------------------------------------------------
contactID | Q1 | Q2 | Q3
-----------------------------------------------------
1 | a. Yes | b. No | option1,option2
2 | b. No | b. No | option2,option3
-----------------------------------------------------
i hope all that makes sense, so what do you guys think would be the best way to do it, maybe a stored procedure to build the query i need? i would appreciate any suggestions because this one has me stumped!
thanks
=========================================
Don't sweat the petty things and don't pet the sweaty things.
i have a database of contacts and surveys, my software is similar to surveymonkey.com where i let people create and use surveys. i need to allow users to export the survey data but since this system is entirely automated, i need to query the survey data without hard-coding any survey columns.
the relevant survey data is like this:
SURVEYS
-surveyID (PK)
-surveyName
PAGES
-pageID (PK)
-surveyID (FK)
-pageOrder
QUESTIONS
-questionID (PK)
-pageID (FK)
-questionOrder
-questionText
QUESTIONOPTIONS
-questionOptionID (PK)
-questionID (FK)
-questionOptionText
-questionOptionOrder
QUESTIONANSWERS (1 question can have multiple answers from the same contact, but not the same option)
-questionAnswerID (PK)
-questionID (FK)
-contactID (FK)
-questionOptionID (FK)
I need the query output to be each question in it's own column, and if there were multiple responses to that question (questionanswers) that they be in a CSV format.
-----------------------------------------------------
contactID | Q1 | Q2 | Q3
-----------------------------------------------------
1 | a. Yes | b. No | option1,option2
2 | b. No | b. No | option2,option3
-----------------------------------------------------
i hope all that makes sense, so what do you guys think would be the best way to do it, maybe a stored procedure to build the query i need? i would appreciate any suggestions because this one has me stumped!
thanks
=========================================
Don't sweat the petty things and don't pet the sweaty things.