Using if else block in pivot query

Question

I have a table

StudentID StudentName Subject Marks
      1   Savita      EC1     50
      1   Savita      EC2     55
      1   Savita      EC3     45
      1   Savita      EC4     34
      1   Savita      EC5     23
      2   Rajesh      EC1     34
      2   Rajesh      EC2     56
      2   Rajesh      EC3     12
      2   Rajesh      EC4     45
      2   Rajesh      EC5     23
      3   Smita       EC1     76
      3   Smita       EC2     45
      3   Smita       EC3     67
      3   Smita       EC4     56
      3   Smita       EC5     76
      4   Rahul       EC1     66
      4   Rahul       EC2     34
      4   Rahul       EC3     22
      4   Rahul       EC4     18
      4   Rahul       EC5     33

I wrote a query like

SELECT StudentName, EC1,EC2,EC3,EC4,EC5,TotalMarks, case  
  when EC1<30 and ec2<30 then 'fail'
  when EC1<30 and EC3<30 then 'fail'
  when EC1<30 and EC4<30 then 'fail'
  when EC1<30 and EC5<30 then 'fail'
  when EC2<30 and EC3<30 then 'fail'
  when EC2<30 and EC4<30 then 'fail'
  when EC2<30 and EC5<30 then 'fail'
  when EC3<30 and EC4<30 then 'fail'
  when EC3<30 and EC5<30 then 'fail'
  when EC4<30 and EC5<30 then 'fail'
  else 'pass'
  end as Result
FROM (SELECT StudentName, EC1, EC2, EC3, EC4, EC5, TotalMarks=EC1+EC2+EC3+EC4+EC5
      FROM Student
      PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5],[TotalMarks]))as pivotTable) A

Which gives an output of students who have less than 30 marks in 2 subjects as fail or else pass

Rahul   66  34  22  18  33  173 fail
Rajesh  34  56  12  45  23  170 fail
Savita  50  55  45  34  23  207 pass
Smita   76  45  67  56  76  320 pass

I want to add 7 marks to each subject which is less than 30 and see the pass fail students after adding 7 marks. eg-After adding 7 marks rajesh record should be like

Rajesh 34 56 19 45 30 170 pass

This question is not DBMS-agnostic. Please indicate which one you are using. — ruakh
– ruakh, Commented Jun 13, 2015 at 4:43
I am new to this so can u please explain what are u asking? i tried to do it in sql server and show the results in an asp.net page. — Ultron_111
– Ultron_111, Commented Jun 13, 2015 at 5:03

James Z · Accepted Answer · 2015-06-13 05:41:59Z

4

Maybe this is something you're looking for:

SELECT A.StudentName, EC1,EC2,EC3,EC4,EC5,Total,
  case when fail2 >= 2 then 'Failure'
       when fail >= 2 then 'Near Pass'
       else 'Pass' end as Result
FROM
(  SELECT StudentName, EC1, EC2, EC3, EC4, EC5
      FROM Student
      PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5]))as pt) A,
(  select
      studentName,
      sum(case when Marks <  30 then 1 else 0 end) as fail,
      sum(case when Marks <  23 then 1 else 0 end) as fail2,
      sum(case when Marks >= 30 then 1 else 0 end) as pass,
      sum(marks) as total
  from
      student
  group by
      studentname
) B
where 
    A.StudentName = B.StudentName

I removed you're comparison logic that was for all the failure combinations and replaced it with sum + group by + case from the original table, so that you can determine the counts for fails, near passes and passes for each student without having to list all the cases separately.

You can test this in SQL Fiddle

answered Jun 13, 2015 at 5:41

James Z

12.3k10 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

James Z Over a year ago

This leaves open the question what should be done if someone doesn't have marks for all the subjects

Ultron_111 Over a year ago

thanks but i want to add 7 grace marks to the subjects which are below 30, and after adding that see if someone has below 30 marks in 2 subjects then fail or else pass. How do i do this?? Everyone have marks for all the subjects .@JamesZ

James Z Over a year ago

@LagnajitPuhan That is what the 'near pass' is doing, it is not adding 7 but comparing to 23, which is the exact same thing.

Giorgos Betsos · Accepted Answer · 2015-06-13 08:06:36Z

You can use windowed versions of SUM, COUNT aggregate functions to simplify your query:

SELECT StudentID, StudentName, 
       [EC1],[EC2],[EC3],[EC4],[EC5], 
       Total,
       CASE WHEN Below30 >= 2 THEN 'Fail'
            ELSE 'Pass'
       END AS WithoutBonus, 
       CASE WHEN Below23 >= 2 THEN 'Fail'
            ELSE 'Pass'
       END AS WithBonus
FROM (SELECT StudentID, StudentName, Subject, Marks,
             SUM(Marks) OVER (PARTITION BY StudentID) AS Total,
             COUNT(CASE WHEN Marks < 30 THEN 1 END) 
                OVER (PARTITION BY StudentID) AS Below30, 
             COUNT(CASE WHEN Marks < 23 THEN 1 END) 
                OVER (PARTITION BY StudentID) AS Below23 
      FROM mytable ) AS src
PIVOT (
   MAX (Marks)
   FOR Subject IN ([EC1],[EC2],[EC3],[EC4],[EC5]) ) pvt
ORDER BY StudentName

Below30 counts the number of subjects having a Mark that is < 30 per StudentID. Below23 counts the number of subjects having a Mark that is < 23 per StudentID.

Hence, WithoutBonus is a 'Fail' if a specific student has 2, or more, subjects with a Mark<30, whereas, WithBonus is a 'Fail' if a specific student has 2, or more, subjects with a Mark<23.

Demo here

If you want to output Marks values incremented by 7, in case they are < 30, and you don't care about WithoutBonus estimate, then you can use the following version:

SELECT StudentID, StudentName, 
       [EC1],[EC2],[EC3],[EC4],[EC5], 
       Total,
       CASE WHEN Below23 >= 2 THEN 'Fail'
            ELSE 'Pass'
       END AS WithBonus
FROM (SELECT StudentID, StudentName, Subject, 
             CASE WHEN Marks < 30 THEN Marks+7 ELSE Marks END AS Marks,
             SUM(Marks) OVER (PARTITION BY StudentID) AS Total,          
             COUNT(CASE WHEN Marks < 23 THEN 1 END) OVER (PARTITION BY StudentID) AS Below23 
      FROM mytable ) AS src
PIVOT (
   MAX (Marks)
   FOR Subject IN ([EC1],[EC2],[EC3],[EC4],[EC5]) ) pvt
ORDER BY StudentName

Output:

StudentID   StudentName EC1 EC2 EC3 EC4 EC5 Total   WithBonus
--------------------------------------------------------------
4           Rahul       66  34  29  25  33  173     Fail
2           Rajesh      34  56  19  45  30  170     Pass
1           Savita      50  55  45  34  30  207     Pass
3           Smita       76  45  67  56  76  320     Pass

Mahedi Sabuj · Accepted Answer · 2015-06-13 08:09:35Z

You can try this

SELECT 
    StudentName,
    CASE WHEN EC1 < 30 THEN EC1 + 7 ELSE EC1 END AS EC1,
    CASE WHEN EC2 < 30 THEN EC2 + 7 ELSE EC2 END AS EC2,
    CASE WHEN EC3 < 30 THEN EC3 + 7 ELSE EC3 END AS EC3,
    CASE WHEN EC4 < 30 THEN EC4 + 7 ELSE EC4 END AS EC4,
    CASE WHEN EC5 < 30 THEN EC5 + 7 ELSE EC5 END AS EC5,
    Total = (EC1 + EC2 + EC3 + EC4 + EC5),
    CASE  
       WHEN EC1 < 23 AND EC2 < 23 THEN 'FAIL'
       WHEN EC1 < 23 AND EC3 < 23 THEN 'FAIL'
       WHEN EC1 < 23 AND EC4 < 23 THEN 'FAIL'
       WHEN EC1 < 23 AND EC5 < 23 THEN 'FAIL'
       WHEN EC2 < 23 AND EC3 < 23 THEN 'FAIL'
       WHEN EC2 < 23 AND EC4 < 23 THEN 'FAIL'
       WHEN EC2 < 23 AND EC5 < 23 THEN 'FAIL'
       WHEN EC3 < 23 AND EC4 < 23 THEN 'FAIL'
       WHEN EC3 < 23 AND EC5 < 23 THEN 'FAIL'
       WHEN EC4 < 23 AND EC5 < 23 THEN 'FAIL'
       ELSE 'PASS'
   END AS Result
FROM
(
   SELECT * FROM Student
) AS ST
PIVOT
(
    SUM(Marks) For [Subject] IN (EC1, EC2, EC3, EC4, EC5)
) AS PV

Output

Rahul   66  34  29  25  33  173 FAIL
Rajesh  34  56  19  45  30  170 PASS
Savita  50  55  45  34  30  207 PASS
Smita   76  45  67  56  76  320 PASS

Collectives™ on Stack Overflow

Using if else block in pivot query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related