SQL Server: advanced report of multiple rows into one

Question

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|label|weight|duration_seconds
========================================================================
bob   |first |loc_nameA|4       |R9   |3.5   |189
bob   |first |loc_nameB|2       |R7   |4.5   |113
…

A report needs to be made showing all responses shown in one row. So for each question I need to display the response into a column along with its corresponding label,weight and duration like this:

userid|lesson|1_resp|1_labl|1_weig|1_dura|2_resp|3_labl|3_weig|3_dura|4_resp…
========================================================================
bob   |first |4     |R9    |3.5   |189   |2     |R7    |4.5   |113   |1

OR alternatively by using the "question" column value as part of the dynamic column name. Currently they all have logical names like L1Q1 so just 1,2,3 will suffice as column names, but this might not always be the case:

userid|lesson|loc_nameA_resp|loc_nameA_labl|loc_nameA_weig|loc_nameA_dura|loc_nameB_resp|loc_nameB_labl|loc_nameB_weig|loc_nameB_dura|loc_nameC_resp…
================================================================================================================================================
bob   |first |4             |R9            |3.5           |189           |2             |R7            |4.5           |113           |1

I have been reading about pivot tables but all examples seem more limited than what I am describing. How can this be done with SQL Server 2005? Should I be using something else? Is there an easier way?

Please tell us more why they seem wrong. Also can you tell us the rules that make the both the outputs the way they are. It's hard to determine requirements from snapshots. Finally this needs to be reported. What are you looking for (1) sql (2) how to lay it out (3) something else — Preet Sangha, Jul 09 '12 at 22:27

Hogan · Accepted Answer · 2012-07-10T20:44:53.767

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 for each 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 for each question
WHERE
   U.userid = 'bob' -- this does not need to be bob, whatever user you want.

Here you go, tested and everything.

DECLARE @sqlSelectList varchar(max);
DECLARE @sqlJoinList varchar(max);

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

WITH Questions AS
(
  SELECT DISTINCT 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

edited Jul 10 '12 at 20:44

answered Jul 10 '12 at 04:23

Hogan

69,564
10
76
117

I will try this. However I know it needs to be more dynamic. Any way to do this as a function or procedure where I don't know what "question" string will be? No where clause necessary. – gooddadmike Jul 10 '12 at 04:44
Yes there is using dynamic sql. Give it a shot – Hogan Jul 10 '12 at 11:22
If you want my help setup some example data and what you have got so far here http://sqlfiddle.com/ – Hogan Jul 10 '12 at 12:18
The only way I could get it to work in fiddle was to change the query terminator to [GO]. I know this has got to work but now I am trying to do it without the CTE b/c ResultsChoices is actualy @ResultsChoices and it gets wiped out on ";". – gooddadmike Jul 11 '12 at 13:23
@lazfish - correct I forgot to note that way to work with sqlfiddle. I deleted the comment about removing the CTE because of the `DISTINCT` -- I think the CTE is the way to go. – Hogan Jul 11 '12 at 15:26
I got it to work by just making the table a real table then dropping it. Thanks! – gooddadmike Jul 11 '12 at 17:06
Good news, I see now you said 2005 -- Sorry about that I remember checking for 2008 but I must have been mistaken. – Hogan Jul 11 '12 at 17:42
No problem sqlfiddle is very nice and thanks for introducing me to it. Also it doesn't support 2005. We are about to make the jump to 2012 finally! – gooddadmike Jul 11 '12 at 17:44

SQL Server: advanced report of multiple rows into one

1 Answers1

Linked