CODEWITHSUNDEEP
sql-server
ghanshyam.mirani
ghanshyam.mirani

Reputation: 3101

Issue Related to Query in SQL Server

I have three table in SQL Server 2008:

Students

    StudentId           Name
      1                 Ghanshyam
      2                 John
      3                 Pravin

Exams

   ExamId               ExamName
     1                  English
     2                  Math
     3                  SS
     4                  Mechanical

Marks

   MarksId       StudentId     ExamId     Marks
       1              1           1         90
       2              1           2         45
       3              1           3         89
       4              1           4         56
       5              1           5         93

I want to get result display in following format:

   Name         English  Math  SS   Mechnical
   Ghanshyam       90     45   89     56
   John            89     38   78     87
   Pravin          98     40   48     38

How can I get the above result based on above three table

Upvotes: 1

Views: 69

Answers (3)

user410039
user410039

Reputation:

Why don't you try something like this, if you do not want a pivot table?

SELECT s.Name, 
       SUM(CASE WHEN e.ExamName =  'English' THEN m.Marks ELSE 0 END) 
         / NULLIF(COUNT(CASE WHEN e.ExamName =  'English' THEN 1 ELSE 0 END), 0) as English,
       SUM(CASE WHEN e.ExamName =  'Math' THEN m.Marks ELSE 0 END)
         / NULLIF(COUNT(CASE WHEN e.ExamName =  'Math' THEN 1 ELSE 0 END), 0) as Math,
       SUM(CASE WHEN e.ExamName =  'SS' THEN m.Marks ELSE 0 END) 
         / NULLIF(COUNT(CASE WHEN e.ExamName =  'SS' THEN 1 ELSE 0 END), 0) as SS,
       SUM(CASE WHEN e.ExamName =  'Mechanical' THEN m.Marks ELSE 0 END)
         / NULLIF(COUNT(CASE WHEN e.ExamName =  'Mechanical' THEN 1 ELSE 0 END), 0) as Mechanical
FROM students s, marks m, exams e
WHERE m.StudentID = s.StudentID
AND m.ExamID = e.ExamID

Upvotes: 0

Reza
Reza

Reputation: 867

This is an example of Pivot tables. See http://msdn.microsoft.com/en-us/library/ms177410.aspx for the explanation of how to do it in MS SQL Server. Note that it is also possible to do this with standard SQL, though a bit more complicated.

Upvotes: 2

Stefan Steiger
Stefan Steiger

Reputation: 82326

Take a look at the pivot operator / Complex pivot example:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

VendorID is your Name, EmpX your subjects.

Upvotes: 1

Related Questions