Many times we need to get a crosslist. Recently, you need this kind of function. For example, you have to get a semester of a class, because the subjects and quantities of each semester change, this requires dynamic queries to get Grades. And get this display form: StudentId StudentName CourseName1 CourseName2 CourseName3 ... 1 Lupenda 66 77 88 ....... . . The following example is to achieve the above function, which involves three tables:
Course Tables: Courseid, CourseName, Duration, [Year], Majorid, Semester
StudentData table: StudentID, StudentName, ClassID ...
Student_Course Table: StudentID, Courseid, Score
- achieved grades according to different parameters - you can get a certain semester, a professional, a class, a student's achievement
Create Procedure GetScore @ Year Int, @ semester bit, @ majorid int = null, @ classid int = null, @ studentId int = nullas
Declare @var varchar (200) Declare @SQL VARCHAR (8000)
Set @SQL = '' - put in the temporary table Select Distinct CourseName Into #tb from course where course.year = @Year and course.semester = @semester
- Declare the cursor declare cur cursorfor select CourseName from #tb open curfetch next from cur into @varwhile @@ fetch_status = 0begin set @sql = @sql 'sum (case when Course.CourseName =' '' @ var '' ' Then Student_course.score else 0 End) AS ' @ var ', 'Fetch next from cur in @var end
Set @SQL = Left (@ SQL, LEN (@SQL) -1)
set @sql = 'select Class.ClassName as class, Student_Course.StudentID as full student number, StudentData.StudentName as name,' @ sql 'from StudentData, Student_Course, Course, Class where Student_Course.CourseID = Course.CourseID and StudentData.StudentID = Student_course.studentid and studentdata.classid = class.classID '- Creating Query Statements IF @Year is not null set @SQL = @ SQL ' and course.Year @ SQL 'and course.year =' cast (@Year as varchar) 4)) - Force convert to strings
IF @semester is not null set @SQL = @ SQL 'and course.semester =' cast (@semester as varchar (1))
IF @majorid is not null set @SQL = @ SQL 'and course.majorid =' casket (@majorid as varchar (4))
IF @classid is not null set @sql = @ SQL 'and studentdata.classid =' cast (@classid as varchar (4))
IF @studentid is not null set @sql = @ SQL 'and studentData.studentId =' cast (@studentid as varchar (8))
Set @SQL = @ SQL 'Group by class.classname, student_course.studentid, studentData.studentname Order by student_course.studentId'
EXEC (@SQL)
DEAALLOCATE CURDROP TABLE #TBGO to this, the access process implements the features we want. First time, please have a lot of support!
