Sql Server: Advanced Report Of Multiple Rows Into One

I have code that puts together data from many tables regarding a users response to all questions of a specific lesson. Responses look like this: userid|lesson|question |response|la

Solution 1:

You can use dynamic SQL to solve this problem -- or if it is just for one set of data write it by hand. In both cases you are going to end up with something that looks like this:

SELECT R1.userid, R1.lesson, 
       R1.response as loc_nameA_resp, R1.lable as loc_nameA_labl, R1.weight as loc_nameA_weig, R1.duration_seconds as loc_nameA_dura,
       R2.response as loc_nameB_resp, R2.lable as loc_nameB_labl, R2.weight as loc_nameB_weig, R2.duration_seconds as loc_nameB_dura,
--- etc foreach question
FROM user U
JOIN response R1 on R1.userid = u.userid and R1.lesson = 'first' and R1.question = 'loc_nameA'JOIN response R2 on R2.userid = u.userid and R2.lesson = 'first' and R2.question = 'loc_nameB'
--- etc foreach question
WHERE
   U.userid = 'bob' -- this does not need to be bob, whatever user you want.

---

Here you go, tested and everything.

DECLARE@sqlSelectListvarchar(max);
DECLARE@sqlJoinListvarchar(max);

SELECT@sqlSelectList='', @sqlJoinList='';

WITH Questions AS
(
  SELECTDISTINCT question
  FROM ResultsChoices
)
SELECT-- We use the question as the alias for join uniqueness,-- We could increment a number but why bother?@sqlJoinList=@sqlJoinList+' JOIN ResultsChoices '+question+' on '+question+'.userid = u.userid and '+question+'.question = '''+question+'''', 
  @sqlSelectList=@sqlSelectList+', '+question+'.response as '+question+'_resp, '+question+'.label as '+question+'_labl, '+question+'.weight as '+question+'_weig, '+question+'.duration_seconds as '+question+'_dura 'FROM Questions;

DECLARE@sql NVARCHAR(max);

SET@sql= N'SELECT DISTINCT u.userid '+@sqlSelectList+ N' FROM #ResultsChoices u '+@sqlJoinList;

EXEC sp_executesql @sql