4

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

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.

0

Hello from 2 years later! I've lived and learned and realized that a key-value model is probably actually an ideal purpose for NoSQL. But our org/team didn't have access to a NoSQL Document DB, past or present.

The way that we have it set up makes it easy to create new "surveys", but difficult to report on. The best solution I can come up with, as efficiently as I can, was to create a view for each survey....

WITH
BestSingers AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS BestSinger
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Best Singer'
),
HottestSingers AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS HottestSinger
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Hottest Singer'
),
RyanSeacreastFans AS
(
  SELECT 
    SS.Id AS SurveySubmissionId,
    SIV.ResponseValue AS IsRyanSeacrestFan
  FROM SurveySubmissions SS
  INNER JOIN SurveyInputValues SIV ON SIV.SurveySubmissionId = SS.Id
  INNER JOIN SurveyInputKeys SIK ON SIV.SurveyInputKeyId = SIK.Id
  WHERE SIK.QuestionKey = 'Do you like Ryan Seacrest?'
)

SELECT
  SS.Id AS SurveySubmissionId,
  BestSingers.BestSinger,
  HottestSingers.HottestSinger,
  RyanSeacrestFans.IsRyanSeacrestFan
FROM SurveySubmissions SS
LEFT JOIN BestSingers ON BestSingers.SurveySubmissionId = SS.Id
LEFT JOIN HottestSingers ON HottestSingers.SurveySubmissionId = SS.Id
LEFT JOIN RyanSeacreastFans ON RyanSeacreastFans.SurveySubmissionId = SS.Id
WHERE SS.SurveyId = 1

But wait...

I got lucky that our "Question Keys" happen to be one-word (PascalCase) without any punctuation/spaces. So check out this dynamic SQL monster, that scrapes the question keys for a given survey and turns them into column headings.

DECLARE @surveyId int = 1;
DECLARE @surveyIdStr nvarchar(10) = CAST(@surveyId AS nvarchar(10));

DECLARE @dynamicCte nvarchar(max) = 'WITH ';

SET @dynamicCte = @dynamicCte + (SELECT STUFF(
  (SELECT 
        N', ' + 
        SIK.[Name] + 's AS ' +
        '
        (
          SELECT 
            SS.Id AS SurveySubmissionId, 
            SIV.JsonValue AS ' + SIK.[Name] + '
          FROM [SurveySubmissions] SS WITH(NOLOCK)
          INNER JOIN [SurveyInputValues] SIV ON SIV.SurveySubmissionId = SS.Id
          INNER JOIN [SurveyInputKeys] SIK ON SIV.SurveyInputKeyId = SIK.Id
          WHERE SIK.Name = ''' + SIK.[Name] + '''
          AND SIK.SurveyId = ' + @surveyIdStr + '
        )
        '
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicCte;


DECLARE @dynamicSelect nvarchar(max) = 
'SELECT TOP 100
    SS.Id AS SurveySubmissionId, ';

SET @dynamicSelect = @dynamicSelect + (SELECT STUFF(
  (SELECT 
        N', ' + 
        SIK.[Name] + 's.' + SIK.[Name]
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicSelect;

SET @dynamicSelect = @dynamicSelect + 
'
    FROM [SurveySubmissions] SS WITH(NOLOCK) 
    INNER JOIN [SurveyResults] SR ON SS.SurveyResultId = SR.Id
';


DECLARE @dynamicJoin nvarchar(max);

SET @dynamicJoin = (SELECT STUFF(
  (SELECT 
        N'  ' + 
        '
            LEFT JOIN ' + SIK.[Name] +'s ON ' + SIK.[Name] + 's.SurveySubmissionId = SS.Id
        '
   FROM [SurveyInputKeys] SIK 
   WHERE SIK.SurveyId = @surveyId
   FOR XML PATH(''), TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N''));
--SELECT @dynamicJoin;


DECLARE @dynamicWhere nvarchar(max) = 
'
    WHERE SS.SurveyId = ' + @surveyIdStr + '
    AND SS.SubmittedDatetime > ''2017-02-24''
    ORDER BY SS.Id DESC
';

DECLARE @dynamicSql nvarchar(max) = @dynamicCte + @dynamicSelect + @dynamicJoin + @dynamicWhere;
--SELECT @dynamicSql;

EXEC SP_EXECUTESQL @dynamicSql;

Psuedo-output...

 SubmissionId  |   BestSinger    |    HottestSinger   |  IsRyanSeacrestFan
      1        |    "Adele"      |    "Katy Perry"    |     true
      2        | "Justin Bieber" |  "Free Form Text"  |     false
      3        |  "John Doe"     |     "Elvis"        |     true

Thoughts? Is this the best of SQL Server mousetraps?

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.