Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
madlo
Starting Member
41 Posts |
 Posted - 2011-08-01 : 12:38:29
|
| I want to create tables that contain dynamic data for me to generate a form without having to create a new column for every question ie. a survey system.My design in a very simple form will look likeQuestion table- QuestionId- DisplayText- QuestionTypeIdOption- OptionId- DisplayQuestionOption- QuestionId- OptionIdUserAnswer-UserId-QuestionId-ChosenOptionIdNow I need to show the survey results per user in a list as following where the columns are the questions and the rows are the answers.User Id, Question1, Question2 .... Question N1, Apple, Yes, ....2. Pear, No, ....The two methods of pivot I know about is xml path and coalesce.Which pivot method of these two or other would be the best practice and performance wise for my solution?Any common good survey database design script/open source db I can resuse? I found [url=http://i.stack.imgur.com/06AEQ.png]this[/url] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
madlo
Starting Member
41 Posts |
Posted - 2011-08-01 : 13:44:54
|
| Yes dynamic - columns must be as many rows are in the db for the questionare. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-01 : 13:49:46
|
| then you can use method in link itself.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Best practice for a survey system