Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

dynamic number of columns - but not hard coded

Status
Not open for further replies.

NorthStarDA

IS-IT--Management
Mar 16, 2004
614
US
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 may be wrong, but I think that what you want needs to be done by your application and cannot be done in MySQL.

It should be relatively easy, in PHP for example, to dynamically create an appropriate SELECT statement to extract the data for your example report.

Andrew
Hampshire, UK
 
hey thanks for the response, i am using coldfusion on this project and figured that i could use coldfusion to generate the selects i need, the problem though is how to get that result without looping over the entire recordset to build the values, that would be slow.

i'm thinking of doing a subselect for each of the questions and in that subselect using group_concat() to pull the values separated by commas. this is the part i'm stuck on- something like

SELECT contacts.*,
(select group_concat(DISTINCT qo.questionOptionText) FROM questionOptions qo INNER JOIN questionAnswers qa ON qa.questionOptionID = qo.questionOptionID INNER JOIN questions q ON q.questionID = qa.questionID WHERE q.questionOrder = '1' GROUP BY qa.questionOptionID) AS q1
FROM contacts

but every attempt at this fails

=========================================
Don't sweat the petty things and don't pet the sweaty things.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top