ERD & SQL Fiddle provided. We're trying to design a reporting tool for our stakeholders around a normalized Key-Value Pair model. We've tried to find a solution using Pivot Tables but can't seem to find a way to accomplish a query in SQL Server.
http://ift.tt/1zgzUXN (SQL Fiddle's server is having some issues.)
Metaphor for the business problem: we have a collection of surveys that collect responses from users. 1 survey has many questions, 1 question has many responses, each of the responses is from a user, and the user has many responses.
A survey can also generate an output of the surveying experience. Each survey has different output keys. 1 survey has many output keys, 1 output key has many values, and each output value is associated with a user.
The input/output values and other data is tied together to the user through a "Survey Submission".
(Random fact: output values and response/input values are both stored as JSON strings to preserve a dynamic data type.)
The stakeholder would like to see the aggregated values for a given survey.
A solution for just the Inputs would be fine, although a solution that works for Inputs and/or Outputs would be great.
Aucun commentaire:
Enregistrer un commentaire