SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Please Help How Can i Pull this off
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nellyihu
Starting Member

Nigeria
8 Posts

Posted - 12/29/2013 :  04:16:22  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 12/29/2013 :  12:54:43  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000