CURSOR like
CreateProc 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
FORSELECTdistinct 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
CREATETABLE Stud_Info
(Enroll_Number varchar(20)NOTNULL,
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)
INSERTinto Stud_Info values(11161,'Mr.','Mack','B','Botha','MECH','Batch1');
INSERTinto Stud_Info values(11162,'Mr.','John','A','Los','CIVIL','Batch2');
INSERTinto Stud_Info values(11163,'Ms.','Merry','F','Dsuza','ELCT','Batch1');
INSERTinto Stud_Info values(11164,'Mr.','Pow','B','Janero','MECH','Batch2');
INSERTinto Stud_Info values(11165,'Mr.','Martin','J','Smith','MECH','Batch1');
SELECT*from Stud_Info
2] Exam_Result
CREATETABLE Exam_Result
(Result_Id numeric(18,0)IDENTITY(1,1)NOTNULL,
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)
INSERTinto Exam_Result values(11161,'Mack','MECH',1,'MT','Maths',25,'Internal1');
INSERTinto Exam_Result values(11161,'Mack','MECH',1,'EN','English',22,'Internal1');
INSERTinto Exam_Result values(11161,'Mack','MECH',1,'SC','Science',20,'Internal1');
INSERTinto Exam_Result values(11161,'MACK','MECH',1,'MT','Maths',21,'Internal2');
INSERTinto Exam_Result values(11161,'Mack','MECH',1,'EN','English',24,'Internal2');
INSERTinto Exam_Result values(11161,'Mack','MECH',1,'SC','Science',22,'Internal2');
SELECT*from Exam_Result
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
=> It works fine with 2 parameters i.e Course_Id and Semester
but i want to trace (through adding 3rd parameter as "Exam_type") with Exam_Type... and looking 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
