dimanche 1 mars 2015

Reporting on a normalized Key-Value pivot


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.)


enter image description here


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