Good day all,pls i am faced with an issue on how to present and format data after selecting from a combination of tables.students in a particular institution register for their courses per semester and per level in a particular department, and then their is need to present the registered courses in a particular format like this belowCourseCode STC 411 STC 412 STC 413 STC 414Credit Unit 2 4 3 2Name of Student and MatricNo Cw TS Ex LG Cw TS Ex LG Cw TS Ex LG Cw TS Ex LG Akuru, Fedrick J. HA08/0471 24 69 45 B 20 53 33 CD 28 61 33 BC 28 61 33 BC DIEKEOCHA, Fedrick J. HA08/0475 24 69 45 B 28 61 33 BC 24 69 45 B 24 69 45 B BUSTA Aake J. HA08/0473 24 69 45 B 28 61 33 BC 28 61 33 BC 20 53 33 CD pls is there a way around this or a walk around?my tables are as follows: 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 CONSTRAINT [DF_Institution_Programme_Course_Registration_Approved] DEFAULT ((0)), [DateApproved] [datetime] NULL, [TestScore] [decimal](18, 2) NULL, [ExamScore] [decimal](18, 2) NULL, [Total] [decimal](18, 2) NULL, [DateRecorded] [datetime] 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]
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 CONSTRAINT [DF_Institution_Programme_Courses_Activated] DEFAULT ((1)), [pass_mark] [numeric](18, 2) NULL DEFAULT ((0)), 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]
CREATE TABLE [dbo].[Institution_Courses]( [CourseId] [int] IDENTITY(1,1) NOT NULL, [CourseCode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [courseName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Activated] [bit] NULL CONSTRAINT [DF_Institution_Courses_Activated] DEFAULT ((1)), 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]
GOCREATE 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) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StartLevel] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EndLevel] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Duration] [int] NULL, [UnitsRequired] [int] NULL, [Activated] [bit] NULL CONSTRAINT [DF_Institution_Programme_Activated] DEFAULT ((1)), [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]goCREATE TABLE [dbo].[Institution_PersonalData]( [PersonalId] [int] IDENTITY(1,1) NOT NULL, [SessionId] [int] NOT NULL, [Surname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MiddleName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DateofBirth] [datetime] NULL, [PlaceofBirth] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Sex] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Religion] [int] NULL, [MaritalStatus] [int] NULL, [PhoneNumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EmailAddress] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NationalityId] [int] NULL, [StateofOrigin] [int] NULL, [LGA] [int] NULL, [HomeTown] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RefCode] [uniqueidentifier] NOT NULL, [DateFilled] [datetime] NULL, [PictureURL] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StatusId] [int] NOT NULL, [contact_address] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ModeOfEntry] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS 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]
and this is how they work,Institution_Courses holds all courses offered in the school,institution_Programme_Courses holds all the courses offered by a programme in a department for example B.Sc marketing would offer MAT 101, MKT 101 , etc.. it is this yables that holds itinstitution_Programme holds all programes offered in a department eg B.Sc marketing in marketing department, M.Sc marketing in Marketing department etc....Insitution_PersonalData holds all students registered in the school and the personalId used in course registration is gotten from this table.note: cw=course work, ex=exam score, ts=total score(cw+ex) lg=letter grade.pls help i really need help with this as quick as possible thanksnellysoft
data presentation in select statement
Posted - 2011-06-14 : 07:46:42
