?
Solved

how can I modify  this SQL?

Posted on 2008-10-29
5
Medium Priority
?
233 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 250 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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 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