SQL Server – Reporting on Normalized Key-Value Pivot

normalizationreportingsql server

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.

Best Answer

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?

Related Question