title: SQL Server: advanced report of multiple rows into one
body: <p>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:</p>
  
  <pre><code>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
  …
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>