hello Gentlemen,
i am currently at a cross road, i have to pull off a report for a university, with all students and their registration number in a row and all the courses they have registered in a session as columns. Now, the interesting thing is that as the rows increase horizontally downwards with the list of students and their registration numbers in a class, the columns increase vertically simultaneously with the courses that was registered, the course units and marks obtained.
please see schema of tables with explanation below:
institution_Courses is where all university courses are held
institution_programme is where all the programmes the university offers are stored
institution_PersonalData is where all the student profiles are held
Institution_Academicprofile is where all the academic profiles of the students are held
Institution_Programme_Courses is where all the courses a programme offeres are held i.e computer science offers csc 101, csc 112, etc…
Institution_Programme_Course_Registration is where all student course registrations are stored across different sessions
CREATE TABLE [dbo].[Institution_Courses](
[CourseId] [int] IDENTITY(1,1) NOT NULL,
[CourseCode] varchar NULL,
[courseName] varchar NULL,
[Activated] [bit] NULL,
CONSTRAINT [PK_Institution_Courses] PRIMARY KEY CLUSTERED
(
[CourseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Institution_Programme](
[ProgrammeId] [int] IDENTITY(1,1) NOT NULL,
[ProgrammeTypeId] [int] NOT NULL,
[DepartmentId] [int] NOT NULL,
[CertificateId] [int] NULL,
[ProgrammeName] varchar NULL,
[StartLevel] varchar NULL,
[EndLevel] varchar NULL,
[Duration] [int] NULL,
[UnitsRequired] [int] NULL,
[Activated] [bit] NULL,
[categoryid] [int] NULL,
CONSTRAINT [PK_Institution_Programme] PRIMARY KEY CLUSTERED
(
[ProgrammeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Institution_PersonalData](
[PersonalId] [int] IDENTITY(1,1) NOT NULL,
[SessionId] [int] NOT NULL,
[Surname] varchar NULL,
[FirstName] varchar NULL,
[MiddleName] nvarchar NULL,
[DateofBirth] [datetime] NULL,
[PlaceofBirth] varchar NULL,
[Sex] char NULL,
[Religion] [int] NULL,
[MaritalStatus] [int] NULL,
[PhoneNumber] varchar NULL,
[EmailAddress] varchar NULL,
[NationalityId] [int] NULL,
[StateofOrigin] [int] NULL,
[LGA] [int] NULL,
[HomeTown] varchar NULL,
[RefCode] [uniqueidentifier] NOT NULL,
[DateFilled] [datetime] NULL,
[PictureURL] varchar NULL,
[StatusId] [int] NOT NULL,
[contact_address] varchar NULL,
[ModeOfEntry] varchar NULL,
CONSTRAINT [PK_Institution_PersonalData] PRIMARY KEY CLUSTERED
(
[PersonalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Institution_AcademicProfile](
[AcademicDetailsId] [int] IDENTITY(1,1) NOT NULL,
[PersonalId] [int] NOT NULL,
[MatricNo] varchar NULL,
[ProgrammeId] [int] NULL,
[CurrentLevel] [int] NULL,
[CurrentSession] [int] NULL,
[EntryRegNo] varchar NULL,
[AcademicStatusId] [int] NULL,
[categoryid] [int] NULL,
[NDGPA] varchar NULL,
[HNDGPA] varchar NULL,
PRIMARY KEY CLUSTERED
(
[AcademicDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Institution_Programme_Courses](
[ProgrammeCourseId] [int] IDENTITY(1,1) NOT NULL,
[ProgrammeId] [int] NOT NULL,
[CourseId] [int] NOT NULL,
[CourseTypeId] [int] NOT NULL,
[CourseUnit] [int] NOT NULL,
[SemesterId] [int] NOT NULL,
[LevelId] [int] NOT NULL,
[Activated] [bit] NULL,
[pass_mark] [numeric](18, 2) NULL,
CONSTRAINT [PK_Institution_Programme_Courses] PRIMARY KEY CLUSTERED
(
[ProgrammeCourseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Institution_Programme_Course_Registration](
[CourseRegId] [int] IDENTITY(1,1) NOT NULL,
[PersonalId] [int] NOT NULL,
[ProgrammeId] [int] NOT NULL,
[LevelId] [int] NOT NULL,
[SemesterId] [int] NOT NULL,
[CourseId] [int] NOT NULL,
[SessionId] [int] NOT NULL,
[DateReg] [datetime] NULL,
[Approved] [bit] NULL,
[DateApproved] [datetime] NULL,
[TestScore] [decimal](18, 2) NULL,
[ExamScore] [decimal](18, 2) NULL,
[Total] [decimal](18, 2) NULL,
[DateRecorded] [datetime] NULL,
[CourseUnit] [int] NULL,
[CourseType] varchar NULL,
CONSTRAINT [PK_Institution_Programme_Course_Registration] PRIMARY KEY CLUSTERED
(
[CourseRegId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Please help as i am at a cross road.
also attached is a sample of the report to be produced
Thanks Gurus.
