sql server - How do I turn this SQL pivot into an inner join pivot? -

i have 3 tables: pupils, ks3assessments , assessmentsets.

• pupils each have studentid, fname, sname etc.
• assessmentset contains title of assessment, deadline, year group must complete it, etc. new ones created throughout year, titles/ids can't named explicitly in sql.
• ks3assessments records each have studentid refers pupil completed work, setid refers relevant assessmentset record , 'nclevel' indicating result pupil achieved.

i need results overview table looks this:

 - studentid ¦ fname ¦ sname ¦ creative writing #1 ¦ novel study ¦ random thingy test ¦ etc. ¦ etc.  - 072509273 ¦ adam¦ adamson¦ 5.5¦ 4.8¦ 6.5¦ etc.¦ etc¦  - 072509274 ¦ bob ¦ bobson¦ 5.8¦ 5.2¦ 7.2¦ etc.¦ etc¦ 

... that, @ time, teacher can see pupil has achieved in whatever assessments they've done far.

so far, using pivot, i've managed this:

 - studentid, fname, sname, 147, 146, 154 (these numbers setids)   - 072509273, adam, adamson, 5.5, 4.8, 6.5  - 072509274, bob, bobson, 5.8, 5.2, 7.2 

here's sql. i'd appreciate ideas how fix , upgrade result i'm looking for. suspect involve inner join (or two), i'm still having trouble getting head around pivot syntax. many thanks.

declare @cols nvarchar(max), @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename(setid)                  ks3assessments         xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')  set @query = 'select studentid, fname, sname' + @cols + '           (             select ks3assessments.studentid,               pupils.fname,               pupils.sname,               ks3assessments.nclevel,               ks3assessments.setid                                                                               ks3assessments inner join pupils on ks3assessments.studentid = pupils.studentid             pupils.groupdesignation = ''8kf/en 14/15''         ) x         pivot (max(nclevel) setid in (' + @cols + ') ) p '  execute(@query) 

try

declare @cols nvarchar(max), @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename(title)                  assessmentset          xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')  set @query = 'select studentid, fname, sname' + @cols + '           (             select ks3assessments.studentid,               pupils.fname,               pupils.sname,               ks3assessments.nclevel,               assessmentset.title             ks3assessments inner join pupils on ks3assessments.studentid = pupils.studentid         inner join assessmentset on ks3assessments.setid = assessmentset.setid             pupils.groupdesignation = ''8kf/en 14/15''         ) x         pivot (max(nclevel) title in (' + @cols + ') ) p '  execute(@query)