Cursor I'm using is...
Create Proc GetExamResults (@Course_Id varchar(100), @Semester varchar(10))
as
begin
declare @subjname varchar(100)
declare @subjects varchar(7000)
declare @subjectsselection varchar(7000)
declare @SumSelection varchar(7000)
declare @NoOfSubjects int
set @NoOfSubjects = 0
set @subjects = ''
set @subjectsselection = ''
set @SumSelection = ''
DECLARE subject_cursor CURSOR
FOR SELECT distinct Subject_Name FROM Exam_Result where course_id = @Course_Id And Semester = @Semester
OPEN subject_cursor
FETCH NEXT FROM subject_cursor
INTO @subjname
WHILE @@FETCH_STATUS = 0
BEGIN
set @subjects = @subjects + '[' + @subjname + '],'
set @subjectsselection = @subjectsselection + 'Sum(Isnull([' + @subjname + '],0)) As [' + @subjname + '],'
set @SumSelection = @SumSelection + 'Sum(Isnull([' + @subjname + '],0))+'
set @NoOfSubjects = @NoOfSubjects + 1
FETCH NEXT FROM subject_cursor
INTO @subjname
End
CLOSE subject_cursor;
DEALLOCATE subject_cursor;
select @subjects = LEFT(@subjects, LEN(@subjects) - 1)
select @subjectsselection = LEFT(@subjectsselection, LEN(@subjectsselection) - 1)
select @SumSelection = LEFT(@SumSelection, LEN(@SumSelection) - 1)
print @subjects
print @subjectsselection
print @SumSelection
declare @query nvarchar(4000)
set @query = 'select S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, ' + @subjectsselection + ','
set @query = @query + 'Exam_Type,' + @SumSelection + ' As Grand_Total, '
set @query = @query + '(' + @SumSelection + ')' + '/' + convert(varchar(10),@NoOfSubjects) + ' As Avg'
set @query = @query + ' From '
set @query = @query + '(select Enroll_Number, Student_Name, Course_Id, Semester, Subject_Name, MarksObtained, Exam_Type from Exam_Result ) ps '
set @query = @query + ' pivot(sum(MarksObtained) for Subject_Name in (' + @subjects + ')) as pvt'
set @query = @query + ' inner join Stud_Info S on S.Enroll_Number = pvt.Enroll_Number '
set @query = @query + ' where pvt.Course_Id = ''' + @Course_Id + ''' and pvt.Semester = ''' + @Semester + ''''
set @query = @query + ' group by S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, Exam_Type'
print @query
exec sp_executesql @query
end
These 2 tables are used in cursor...
1] Stud_Info
CREATE TABLE Stud_Info
(Enroll_Number varchar(20) NOT NULL,
Salutation varchar(10) NULL,
First_Name varchar(20) NULL,
Middle_Name varchar(20) NULL,
Last_Name varchar(20) NULL,
Course_Id varchar(20) NULL,
Batch varchar(20) NULL)
INSERT into Stud_Info values(11161,'Mr.','Mack','B','Botha','MECH','Batch1');
INSERT into Stud_Info values(11162,'Mr.','John','A','Los','CIVIL','Batch2');
INSERT into Stud_Info values(11163,'Ms.','Merry','F','Dsuza','ELCT','Batch1');
INSERT into Stud_Info values(11164,'Mr.','Pow','B','Janero','MECH','Batch2');
INSERT into Stud_Info values(11165,'Mr.','Martin','J','Smith','MECH','Batch1');
2] Exam_Result
CREATE TABLE Exam_Result
(Result_Id numeric(18, 0) IDENTITY(1,1) NOT NULL,
Enroll_Number varchar(50) NULL,
Student_Name varchar(100) NULL,
Course_Id varchar(50) NULL,
Semester varchar(50) NULL,
Subject_Id varchar(50) NULL,
Subject_Name varchar(50) NULL,
MarksObtained int NULL,
Exam_Type varchar(50) NULL)
INSERT into Exam_Result values(11161,'Mack','MECH',1,'MT','Maths',25,'Internal1');
INSERT into Exam_Result values(11161,'Mack','MECH',1,'EN','English',22,'Internal1');
INSERT into Exam_Result values(11161,'Mack','MECH',1,'SC','Science',20,'Internal1');
INSERT into Exam_Result values(11161,'MACK','MECH',1,'MT','Maths',21,'Internal2');
INSERT into Exam_Result values(11161,'Mack','MECH',1,'EN','English',24,'Internal2');
INSERT into Exam_Result values(11161,'Mack','MECH',1,'SC','Science',22,'Internal2');
Here I'm passing it with 2 parameters suppose I'm passing GetExamResults 'MECH','2'
Then result comes like...
Enroll_Number Student_Name Course_Id Semester Maths English Science **Exam_Type** total avg
11161 Mack MECH 1 25 22 20 **Internal1** 67 total/all sub
11161 Mack MECH 1 21 24 22 **Internal2** 67 total/all sub
But I want to trace (through adding 3rd parameter for Exam_type) with Exam_Type... and look in for result something like.....
When user execute GetExamResults 'MECH','1','Internal1' then result should come like...
Enroll_Number Student_Name Course_Id Semester Maths English Science **Exam_Type** total avg
11161 Mack MECH 1 25 22 20 **Internal1** 67 total/all sub
and when user execute GetExamResults 'MECH','1','Internal2' then result should come like...
11161 Mack MECH 1 21 24 22 **Internal2** 67 total/all sub
Here. I've added an optional parameter @Exam_Type. If you do not specify value, or set null, procedure will return all rows.
Cursor definition changed to include LOCAL and FAST_FORWARD to speed up execution. And (@Exam_Type is null OR @Exam_Type = Exam_Type) added to filter exam_results by value of @Exam_Type; if null, no filtering occurs. The same filter is added to (select Enroll_Number, Student_Name, dynamic sql line if @Exam_Type is not null.
ALTER Proc GetExamResults (@Course_Id varchar(100), @Semester varchar(10), @Exam_Type varchar(50) = null)
as
begin
declare @subjname varchar(100)
declare @subjects varchar(7000)
declare @subjectsselection varchar(7000)
declare @SumSelection varchar(7000)
declare @NoOfSubjects int
set @NoOfSubjects = 0
set @subjects = ''
set @subjectsselection = ''
set @SumSelection = ''
DECLARE subject_cursor CURSOR LOCAL FAST_FORWARD
FOR SELECT distinct Subject_Name FROM Exam_Result where course_id = @Course_Id And Semester = @Semester And (@Exam_Type is null OR @Exam_Type = Exam_Type)
OPEN subject_cursor
FETCH NEXT FROM subject_cursor
INTO @subjname
WHILE @@FETCH_STATUS = 0
BEGIN
set @subjects = @subjects + '[' + @subjname + '],'
set @subjectsselection = @subjectsselection + 'Sum(Isnull([' + @subjname + '],0)) As [' + @subjname + '],'
set @SumSelection = @SumSelection + 'Sum(Isnull([' + @subjname + '],0))+'
set @NoOfSubjects = @NoOfSubjects + 1
FETCH NEXT FROM subject_cursor
INTO @subjname
End
CLOSE subject_cursor;
DEALLOCATE subject_cursor;
select @subjects = LEFT(@subjects, LEN(@subjects) - 1)
select @subjectsselection = LEFT(@subjectsselection, LEN(@subjectsselection) - 1)
select @SumSelection = LEFT(@SumSelection, LEN(@SumSelection) - 1)
print @subjects
print @subjectsselection
print @SumSelection
declare @query nvarchar(4000)
set @query = 'select S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, ' + @subjectsselection + ','
set @query = @query + 'Exam_Type,' + @SumSelection + ' As Grand_Total, '
set @query = @query + '(' + @SumSelection + ')' + '/' + convert(varchar(10),@NoOfSubjects) + ' As Avg'
set @query = @query + ' From '
set @query = @query + '(select Enroll_Number, Student_Name, Course_Id, Semester, Subject_Name, MarksObtained, Exam_Type from Exam_Result ' + (case when @Exam_Type is null then '' else ' Where Exam_Type =''' + @Exam_Type + '''' end) + ' ) ps '
set @query = @query + ' pivot(sum(MarksObtained) for Subject_Name in (' + @subjects + ')) as pvt'
set @query = @query + ' inner join Stud_Info S on S.Enroll_Number = pvt.Enroll_Number '
set @query = @query + ' where pvt.Course_Id = ''' + @Course_Id + ''' and pvt.Semester = ''' + @Semester + ''''
set @query = @query + ' group by S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, Exam_Type'
print @query
exec sp_executesql @query
end