Need a feedback for matrix question table design

Question

Hi

In the survey, there is a type of question called Matrix which it's like this:

          | Is Friendly | Weather | Comments
===========================================
Sydney    | Y           | 5       | 'bla'
-------------------------------------------
Singapore | Y           | 10      | 'test'
-------------------------------------------
Jakarta   | N           | 0       | 'test2
-------------------------------------------

Try to get a feedback in term of designing SQL table for question and answer. I could have a design that you can only have 3 label sets (Is Friendly, Weather, Comment) or maybe extended to 10 to be save which means I have 10 columns.

What do you think about this approach, I know this is not relation database in such but at least from query point of view for answer to pull out.

Your thought?

Answer 1

In Sql Server you can make use of PIVOT.

This will allow you to design the table differently.

You would then have a table with columns

• EntryType (eg. IsFriendly, Weather, Comment)
• City_Region (eg. Sydney, Singapore, Jakarta)
• EntryValue (eg. Y, 5, bla)

This will basically give you the functionality to have "dynamic" columns.