Reporting on a normalized Key-Value pivot ERD &amp; 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://sqlfiddle.com/#!2/6ed2f (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.
A friend offered me this solution, although he doesn't have an account for SE.  

Note from 2 years later: This provides the number of times each answer occurs per question. Which is kind of helpful, but doesn't tell us, "what are all of the questions and answers given on this one survey submission?" Maybe I should have emphasized that 'ResponseValue' can be free-form text.

SELECT SIK.Name AS [QuestionKey], SIV.JsonValue AS [ResponseValue], COUNT(SIV.Id) AS [Frequency] 
FROM [Surveys] S 
JOIN [SurveyInputKeys] SIK ON SIK.SurveyId = S.Id
JOIN [SurveyInputValues] SIV ON SIV.SurveyInputKeyId = SIK.Id
WHERE S.Id = @surveyId
GROUP BY SIK.Id, SIV.JsonValue, SIK.Name


Psuedo-output...


   SurveyId    |   QuestionKey   |   ResposeValue   |  Frequency
      1        |   Best Singer   |     "Adele"      |     5
      1        |   Best Singer   |   "John Doe"     |     2
      1        |   Best Singer   | "Free Form Text" |     1