select ROW_NUMBER() OVER (ORDER BY t.studentid) AS SNo,max(t.roll_no) as RollNo,max(t.student) as Student , max([MST1(10)]) as [1 MST1(10)],max([MST2(20)]) as [2 MST2(20)] From ( select t.studentid,'' as roll_no,'' as student,'' as subject ,'' as course,t.sem , [1] as [MST1(10)],[2] as [MST2(20)] from exammarksheet pivot (max (obtMarks) for ExamID in ([1],[2])) as t where (t.subjectid = 2 And t.courseid = 1492 And t.sem = 3 And t.classesid = 17 And t.sessionid = 20142015) union all select distinct student.studentid, roll_no,student ,subjectall.code as subject,course.code as course,exammarksheet.sem , '' as a,'' as a,'' as a from student inner join exammarksheet on student.studentid=exammarksheet.studentid inner join course on student.courseid=course.courseid inner join subjectall on exammarksheet.subjectid=subjectall.subjectid where exammarksheet.subjectid = 2 And exammarksheet.courseid = 1492 And exammarksheet.sem = 3 and exammarksheet.sessionid=20142015 And exammarksheet.classesid = 17 ) as t group by t.sem,t.studentid
([MST1(10)] + [MST2(20)]) as Total
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)