Solved

how can I modify  this SQL?

Posted on 2008-10-29
5
201 Views
Last Modified: 2011-10-19
Table below shows the marks obtained by a particular student in the final
exam.SQL for the below table

select s.StudentName,sm.SubjectName,sm.Marks,sm.TotalMarks,sm.Grade from
StudentMaster s,SubjectMaster sm where s.studentid=sm.studentid order by StudentName

StudentName SubjectName Marks TotalMarks Grade      
-----------------------------------------------------------------------
STDABC            Islamic Culture            97      100            A
STDABC            Biology                  71      100            C
STDABC            History                  90      100            A
STDABC            Geography            88      100            B+
STDABC            Applied Math            75      100            C+
STDABC            Drawing                  89      100            B+
STDABC            Physical Education      90      100            A
-----------------------------------------------------------------------
STDMNO            History                  91      100            A
STDMNO            Biology                  81      100            B
STDMNO            Pure Math             59      100            D
STDMNO            Drawing                  98      100            A
STDMNO            Physical Education      75      100            C+
-----------------------------------------------------------------------
STDPQR            History                  93      100            A
STDPQR            Biology                  84      100            B
STDPQR            Applied Math             53      100            D
STDPQR            Physical Education      72      100            C+
STDPQR            Geography            83      100            B+
STDPQR            Drawing                  94      100            A
-----------------------------------------------------------------------

We have 15 subjects in total and each students have different choice of subject marks.

Also student name is repeated depending on the number of subjects.

Taken 8 subjects as an example we need an SQL which displays the above data in the fomat below.

StudentName      Islamic Culture      Biology      History      Geography      Applied Math      Drawing      PhysicalEducation   Pure Math      TotalMarks
------------------------------------------------------------------------------------------------------------------------------------------
STDABC            97            71      90      88            75            89      90                0            100      
STDMNO            0            81      91      0            0            98      75                59            100
STDPQR            0            84      93      83            53            94      72                0             100

If the student subject marks is not in the subject name list replace with a value zero.
I attach the sql output to make it clear


SQLRequest1.txt
0
Comment
Question by:albusaidi
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22829110
You can use PIVOT table command
0
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22829121
0
 
LVL 3

Accepted Solution

by:
Blackninja2007 earned 125 total points
ID: 22829420
This should start you off ... you just need to put all subjects in first select and at the bottom where i have put ... etc

select studName,  [Islamic Culture] as s1, [Biology] as s2, [History] as s3, ... etc
from
(select s.StudentName as studName,sm.SubjectName as subName, sm.Marks as mark, sm.TotalMarks as totMark, sm.Grade as grad
 from StudentMaster s
 inner join SubjectMaster sm on s.studentid=sm.studentid ) p
 PIVOT
 (
  sum(mark)
  for subName in
  ( [Islamic Culture], [Biology], [History], ... etc  )
) as pvt
0
 

Author Comment

by:albusaidi
ID: 22860713
I did this
select s.StudentName,
 (case when sb.SUBJECTID=1 then sm.marks else 0 end) Islamic_Culture,
 (case when sb.SUBJECTID=2 then sm.marks else 0 end) Biology,
 (case when sb.SUBJECTID=3 then sm.marks else 0 end) History,
 (case when sb.SUBJECTID=4 then sm.marks else 0 end) Geography  ,
 (case when sb.SUBJECTID=5 then sm.marks else 0 end) Applied Math,
 (case when sb.SUBJECTID=6 then sm.marks else 0 end) PureMath,
 (case when sb.SUBJECTID=7 then sm.marks else 0 end)Drawing ,
 (case when sb.SUBJECTID=8 then sm.marks else 0 end) PhysicalEducation,
 from SubjectMaster sm,STUDENTMARK sm,StudentMaster s where sm.STUDENTID=s.STUDENTID and sm.SUBJECTID=sb.SUBJECTID

But this results in getting multiple rows as opposed to the format required above.

STDABC      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
STDABC      0.00      0.00      0.00      0.00      0.00      51.00      0.00      0.00
STDABC      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
STDABC      0.00      0.00      0.00      77.00      0.00      0.00      0.00      0.00
STDABC      0.00      0.00      0.00      0.00      0.00      0.00      74.00      0.00
STDABC      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
STDABC      0.00      50.00      0.00      0.00      0.00      0.00      0.00      0.00
STDABC      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00


0
 

Author Comment

by:albusaidi
ID: 22860728
I suppose to get it in one line only
STDABC      0.00     50.00      0.00      77.00      0.00      51.00      74.00      0.00

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now