tsql - Pivot table and combine records onto 1 line -

i've tried many different ways pivot table show records on 1 row. i've provided query closest solution came with. it'll easier if illustrate need. since there can unlimited number of teacher survey questions query has dynamic. i've modified column names make easier read.

teachersurveyquestions

tsqid    cid    order    oqreference    stem 1        1011   1        q1_rb          blabla 2        1011   2        q2_rb          blabla 3        1011   3        q2a_cb         blabla 

teachersurveyuserid

tsuid    firstname    lastname    uid 1        bob          smith       1027 2        tom          jones       1034 

teachersurveyanswers

tsaid    uid    tsqid    tsuid    response 1        1027   1        1        bob 1 2        1027   2        1        bob 2 3        1027   3        1        bob 3 4        1034   1        2        tom 1 5        1034   2        2        tom 2 6        1034   3        2        tom 3 

now need data this:

firstname    lastname    q1_rb    q2_rb    q2a_cb bob          smith       bob 1    bob 2    bob 3 tom          jones       tom 1    tom 2    tom 3 

here's have far kind of works except responses null

declare  @query nvarchar(max),          @colspivot nvarchar(max)  select @colspivot = stuff((select ','                             + quotename(oqreference)                             teachersurveyquestions tsq                            tsq.cid = 1011                            order tsq.order                     xml path(''), type                     ).value('.', 'nvarchar(max)')                      ,1,1,'')  set @query  = 'select *   (     select firstname, lastname, value, col +''_''+ cast(rn varchar(10)) col           (        select          tsu.tsuid            ,tsu.firstname          ,tsu.lastname          ,tsq.oqreference          ,tsa.response          ,row_number() over(partition tsu.tsuid order tsq.order) rn        teachersurveyquestions tsq         inner join teachersurveyanswers tsa on tsa.tsqid = tsq.tsqid        inner join teachersurveyusers tsu on tsu.tsuid = tsa.tsuid        tsq.cid = 1011    ) x    unpivot    (      value      col in (oqreference)    ) u   ) x1   pivot   (     max(value)     col in ('+ @colspivot +')   ) p'  exec(@query) 

result of query:

firstname    lastname    q1_rb    q2_rb    q2a_cb bob          smith       null     null     null tom          jones       null     null     null 

try this

declare  @query nvarchar(max),          @colspivot nvarchar(max)  select @colspivot = stuff((select ','                             + quotename(oqreference)                             teachersurveyquestions tsq                            tsq.cid = 1011                            order tsq.[order]                     xml path(''), type                     ).value('.', 'nvarchar(max)')                      ,1,1,'')    set @query  = 'select firstname, lastname,'+ @colspivot +'   (     select firstname, lastname,response, value           (        select          tsu.tsuid            ,tsu.firstname          ,tsu.lastname          ,tsq.oqreference          ,tsa.response          ,row_number() over(partition tsu.tsuid order tsq.[order]) rn        teachersurveyquestions tsq         inner join teachersurveyanswers tsa on tsa.tsqid = tsq.tsqid        inner join teachersurveyuserid tsu on tsu.tsuid = tsa.tsuid        tsq.cid = 1011    ) x    unpivot    (      value      col in (oqreference)    ) u   ) x1   pivot   (     max(response)     value in ('+ @colspivot +')   ) p'  exec(@query) 

sql fiddle demo