Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how can I modify  this SQL?

Posted on 2008-10-29
5
219 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 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