how to calculate Percentage from Dynamic Pivot Query in Sql

Question

i want to show every subject's marks in front of students and also percentage of total subjects.

For this Purpose i make dynamic pivot query this query gives me all subjects but not percentage actually i do not know how to calculate percentage because my columns are dynamic

    Declare @cols AS nvarchar(max),
    @query as nvarchar(max)

Select @cols = Stuff(
    (   
     Select distinct ','+ QUOTENAME(SubNameUr) 
     from TblSubject Sub
     Inner join TblSubClassRel subr on Sub.SubId = subr.SubId
     inner join TblClass C on subr.ClassID = C.ClassID
     inner join  TblClassSchedule csh on subr.ClassID = csh.ClassID
     inner join TblSubAss sba on subr.ClassSubRelId = sba.ClassSubRelId
     inner join TblSemAssigning SemA on csh.SemAssId = SemA.SemAssId
     inner join TblAcademicYear Ay on SemA.AcademicYearId = Ay.AcademicYearId 
     Where csh.ClassSchID = 207 and Ay.AcademicYearName ='2008-09'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

Set @query = ' Select pivot1.FullName,pivot1.ClassName, ' + @cols + ' 

          From
          (
            Select distinct S.First_NameUr  + S.Last_NameUr as FullName,c.ClassName,
            sub.SubNameUr,
            subr.obtainedMarks,sd.StudentDetailId--,(SUM(obtainedMarks)/COUNT(sd.StudentDetailId))as Percentage
            From TblStudentBioData s
            inner join TblStudentDetail sd on s.Student_ID = sd.Student_ID
            inner join TblStudentSubAss ssb on sd.StudentDetailID = ssb.StudentDetailID
            inner join TblClassSchedule csh on sd.ClassSchId = csh.ClassSchID
            inner join TblSubAss sba on ssb.SubAssId = sba.SubAssId 
            inner join TblClass c on csh.ClassID = c.ClassID
            inner join TblSubClassRel sc on sba.ClassSubRelId = sc.ClassSubRelId
            inner join TblSubject sub on sc.SubId = sub.SubId
            inner join TBLSubResult subr on ssb.StudentSubAssID = subr.StudentSubAssID
            where csh.ClassSchID =207
            group by S.First_NameUr,S.Last_NameUr,c.ClassName,sub.SubNameUr,subr.obtainedMarks,sd.studentdetailId
    ) tem   
    pivot
    (
        max(obtainedMarks)
        for SubNameUr in('+@cols+')

    )pivot1 '

above Query Gives me following Output

Answer 1

You may want something like this:

SUM(obtainedMarks) OVER(PARTITION BY sd.StudentDetailId) / COUNT(sd.StudentDetailId) OVER(PARTITION BY sd.StudentDetailId) AS Percentage