Solved

how can I modify  this SQL?

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 …
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 tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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 …

773 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