SQL Server: advanced report of multiple rows into one -

I have a code that answers all questions of a specific lesson about users from multiple tables together with data The responses appear as follows:

  userid | Text | Questions | Feedback | Label | Weight | Duration_second ================================================= ====================== Bob | First | Loc_nameA | 4 | R9 | 3.5 | 189 Bob | First | Loc_nameB | 2 | R7 | 4.5 | 113 â ?? |   
 A report showing the need to create all the responses in one line. | 1_resp | 1_labl | 1_weig | 1_dura | 2_resp | 3_labl  
  User ID: For each question, I need to display the response in a column with its corresponding label, weight and the duration like this. 3_weig | 3_dura | 4_respâ? | | ================================================== ==================== Bob | First | 4 | R9 | 3.5 | 18 9 2 | R7 | 4.5 | 113 | 1   
 Or alternatively using the "Question" column value as part of the dynamic column name Currently all of them are just logical names like L1Q1 that will be enough as the 1,2,3 column name, but this can not always be:  
  userid | Lessons | 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 | 18 9 2 | R7 | 4.5 | 113 | 1   
 I am reading about the pivot table but all the examples seem more limited than what I am describing. How can this be done with SQL Server 2005? Should I use something else? Is there an easier way?   
 
 
 You can use dynamic SQL to solve this problem - or if it is just Handwriting it for a set of data In both cases you are going to end up with something that looks like this:  
  select r1.userid, r1.lesson , R1.response loc_nameA_resp as, loc_nameA_labl, R1.weight form R1.lable loc_nameA_weig, loc_nameA_dura as R1.duration_seconds, R2.response loc_nameB_resp, loc_nameB_labl as R2.lable, loc_nameB_weig as R2.weight, loc_nameB_dura R2. Duration_seconds, as --- Join each question of the subject on the U R1.userid for feedback R1 etc = u.userid and R1.lesson = 'first' and R1.question = 'loc_nameA' R2.userid = u.userid and R2.lesson = Be feedback r2 'first' and r2.question = 'loc_nameb' --- etc. For each question where u Eurered = 'Bob' - It does not have to be a bob, the user you want.   
 
 Here you go, test and do everything.  
  DECLARE @sqlSelectList varchar (max); DECLARE @sqlJoinList varchar (max); SELECT @sqlSelectList = '', @ sqlJoinList = ''; As with the question (choose a different question from selecting the results) SELECT - We use the question as the nickname to join the specification, - we can increase a number but why bother? @sqlJoinList = @sqlJoinList + + question '+ question +' Userid = u.userid and '+ question +'. Question = '' '+' Include ResultsChoices' + Question + '' ', @sqlSelectList = @sqlSelectList +', as' + question + '_ resp,' + question + '. As '+ question +' _ labl label, '+ question +'. As' + question + '_ weig weighs,' + question + '+ + + question +' reaction. As a question from '+ question +' DURATION_SECONDS _dura '; DECLARE @sql NVARCHAR (max); #ResultsChoices from youtube @sqlJoinList SET @sql = N'strict DISTINCT u.userid '+ @sqlSelectList + N'; EXEC sp_executesql @sql