0

I have 3 tables namely Question, Feedback and FeedbackResult. I wish to generate a report where I need to find the rating average against each question. 

Questions
---------
QuestionId  Question
1           Question 1
2           Question 2
3           Question 3
4           Question 4
5           Question 5

Feedback
--------
FeedbackId  Questions   QuestionCount
2           1,2,3,4,5   5                 -- Questions column has questionid from Question table

FeedbackResults
---------------
FeedbackResId   FeedbackId  Answers
1               2           4,3,5,2,3      -- these answers are ratings(1 to 5) against each question
2               2           4,2,3,4,5      -- which means 4 is the rating for QuestionId 1, 2 is for QuestionId 2 etc
3               2           5,3,4,3,2
4               2           4,1,1,1,2

I wish to get result as average rating against each question

Question        Rating
Question 1      3.5
Question 2      4
Question 3      5
Question 4      2
Question 5      4.5

Edit

Should I redesign the database table as

 FeedbackResults
    ---------------
    FeedbackResId   FeedbackId  QuetionID Answers    UserId
    1               2           4          4         1
    2               2           1          3         1
    3               2           5          3         1
    4               2           1          2         2
Dale K
  • 25,246
  • 15
  • 42
  • 71
Surensiveaya
  • 297
  • 1
  • 12
  • 11
    Fix your data model! You should not be storing numbers as strings! You should not be storing multiple values in a string! – Gordon Linoff Nov 30 '19 at 13:30
  • 1
    You also have the problem here of wanting to extract the values **and** retaining and knowing their original position; something that isn't built in to SQL Server at all. Fixing your design is by far the correct answer here. Of course, we're happy to show you how to achieve this, but you should let us know you have the capacity to fix it. If you don't, then you need to start looking at when you can, as you're going to very quickly find the design more than problematic. – Thom A Nov 30 '19 at 14:20
  • You mean to save questionid,user and rating in one table? – Surensiveaya Dec 01 '19 at 11:09
  • 2
    Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Dec 01 '19 at 11:36
  • could you check my answer – Surensiveaya Dec 01 '19 at 13:31
  • posted for review https://codereview.stackexchange.com/questions/233253/database-model-for-feedback-module – Surensiveaya Dec 02 '19 at 06:00

0 Answers0