Please Help How Can i Pull this off

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Please Help How Can i Pull this off

Author

Topic

nellyihu
Starting Member

8 Posts

Posted - 2013-12-29 : 04:16:22

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](20) NULL,
	[courseName] [varchar](150) 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](500) NULL,
	[StartLevel] [varchar](50) NULL,
	[EndLevel] [varchar](50) 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](50) NULL,
	[FirstName] [varchar](50) NULL,
	[MiddleName] [nvarchar](50) NULL,
	[DateofBirth] [datetime] NULL,
	[PlaceofBirth] [varchar](50) NULL,
	[Sex] [char](1) NULL,
	[Religion] [int] NULL,
	[MaritalStatus] [int] NULL,
	[PhoneNumber] [varchar](50) NULL,
	[EmailAddress] [varchar](50) NULL,
	[NationalityId] [int] NULL,
	[StateofOrigin] [int] NULL,
	[LGA] [int] NULL,
	[HomeTown] [varchar](50) NULL,
	[RefCode] [uniqueidentifier] NOT NULL,
	[DateFilled] [datetime] NULL,
	[PictureURL] [varchar](100) NULL,
	[StatusId] [int] NOT NULL,
	[contact_address] [varchar](max) NULL,
	[ModeOfEntry] [varchar](50) 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](50) NULL,
	[ProgrammeId] [int] NULL,
	[CurrentLevel] [int] NULL,
	[CurrentSession] [int] NULL,
	[EntryRegNo] [varchar](50) NULL,
	[AcademicStatusId] [int] NULL,
	[categoryid] [int] NULL,
	[NDGPA] [varchar](10) NULL,
	[HNDGPA] [varchar](10) 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](20) 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.

Thanks Gurus.

nellysoft

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-29 : 12:54:43

see the link below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources