Solved

how can I modify  this SQL?

Posted on 2008-10-29
5
229 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
[X]
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
  • 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 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