preparing marklist of students - from Master & Sub

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

General SQL Server Forums

New to SQL Server Programming

preparing marklist of students - from Master & Sub

Author

Topic

sheriefes
Starting Member

9 Posts

Posted - 2012-04-30 : 19:13:46

I want to prepare mark list of students in a class

2 tables are used for storing data (SQL server 2005) - Mark Master & MarkSub

Mark Master

mmId - Primary key
subject

MarkSub

mmId - Foreign key
student
mark

Assume total 5 subjects, and class contains 25 students

The format of marklist

Student subject1 subject2 subject3 subject4 subject5 Total

How can I prepare the list from the above table structure

‹ Previous Thread|Next Thread ›

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-04-30 : 19:17:32

I don't even understand the question, but this smells of homework/test question. If you want help, show us what you have so far.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sheriefes
Starting Member

9 Posts

Posted - 2012-04-30 : 20:42:25

Dear Kaizar

I am developing a VB.net application for College
Exam Marks obtained in each subject is entered

which is stored into 2 tables
Markmaster
mmId subject
1 Maths
2 phyicis
3 Chemistry
4 Enlish
5 CS

Corresponding to one record in Markaster, murksub have as many records as number students in the class
Marksub

mmId student marksObtained
1 abc 75
1 xyz 70
1 pqr 75
1 dfh 90
.
2 abc 65
2 xyz 75
2 pqr 80
2 dfh 45
.
3 abc 55
3 xyz 79
3 pqr 87
dfh 95
and so on, hope now u got the point
.

sheriefes
Starting Member

9 Posts

Posted - 2012-04-30 : 20:50:54

Dear kaizr

Sorry one more thing

The output of marklist is follows

Student subject1 subject2 subject3 Total
abc 75 65 55 195
xyz 75 70 79 229
pqr 75 80 87 242

and so on

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-01 : 06:39:02

This might help:


--Create Table MarkMaster

Create Table MarkMaster
(mmId int,
 subject varchar(30) )


--Insert Sample Data Into MarkMaster(Removed data for student abc just to make it a little easy)
 
Insert Into MarkMaster
Select 1, 'Maths'
Union ALL
Select 2, 'phyicis'
Union ALL
Select 3, 'Chemistry'
Union ALL
Select 4, 'Enlish'
Union ALL
Select 5, 'CS'


--Create Table MarkSub

Create Table MarkSub
(mmId int,
 student varchar(10),
 marksObtained int )


--Insert Sample Data Into MarkSub

Insert Into MarkSub
Select 1, 'abc', 75
Union ALL
Select 1, 'xyz', 70
Union ALL
Select 1, 'pqr', 75
Union ALL
Select 1, 'dfh', 90
Union ALL
Select 2, 'xyz', 75
Union ALL
Select 2, 'pqr', 80 
Union ALL
Select 2, 'dfh', 45
Union ALL
Select 3, 'xyz', 79
Union ALL
Select 3, 'pqr', 87 
Union ALL
Select 3, 'dfh', 95
Union ALL
Select 4, 'xyz', 89
Union ALL
Select 4, 'pqr', 47 
Union ALL
Select 4, 'dfh', 65
Union ALL
Select 5, 'abc', 85
Union ALL
Select 5, 'xyz', 49
Union ALL
Select 5, 'pqr', 57 
Union ALL
Select 5, 'dfh', 65


--Required Query

Select p.student, p.Maths, p.phyicis, p.Chemistry, p.Enlish, CS From
(Select a.student, b.subject, a.marksObtained From MarkSub As a
JOIN MarkMaster as b ON a.mmId = b.mmId) As t
Pivot
(MAX(marksObtained) For Subject In (Maths, phyicis, Chemistry, Enlish, CS)) as p