sql - Using if else block in pivot query -

i have 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 query like

select studentname, ec1,ec2,ec3,ec4,ec5,totalmarks, case     when ec1<30 , ec2<30 'fail'   when ec1<30 , ec3<30 'fail'   when ec1<30 , ec4<30 'fail'   when ec1<30 , ec5<30 'fail'   when ec2<30 , ec3<30 'fail'   when ec2<30 , ec4<30 'fail'   when ec2<30 , ec5<30 'fail'   when ec3<30 , ec4<30 'fail'   when ec3<30 , ec5<30 'fail'   when ec4<30 , ec5<30 'fail'   else 'pass'   end result (select studentname, ec1, ec2, ec3, ec4, ec5, totalmarks=ec1+ec2+ec3+ec4+ec5       student       pivot(sum(marks) subject in([ec1],[ec2],[ec3],[ec4],[ec5],[totalmarks]))as pivottable) 

which gives output of students have less 30 marks in 2 subjects 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 add 7 marks each subject less 30 , see pass fail students after adding 7 marks. eg-after adding 7 marks rajesh record should like

rajesh 34 56 19 45 30 170 pass

you can try this

select      studentname,     case when ec1 < 30 ec1 + 7 else ec1 end ec1,     case when ec2 < 30 ec2 + 7 else ec2 end ec2,     case when ec3 < 30 ec3 + 7 else ec3 end ec3,     case when ec4 < 30 ec4 + 7 else ec4 end ec4,     case when ec5 < 30 ec5 + 7 else ec5 end ec5,     total = (ec1 + ec2 + ec3 + ec4 + ec5),     case          when ec1 < 23 , ec2 < 23 'fail'        when ec1 < 23 , ec3 < 23 'fail'        when ec1 < 23 , ec4 < 23 'fail'        when ec1 < 23 , ec5 < 23 'fail'        when ec2 < 23 , ec3 < 23 'fail'        when ec2 < 23 , ec4 < 23 'fail'        when ec2 < 23 , ec5 < 23 'fail'        when ec3 < 23 , ec4 < 23 'fail'        when ec3 < 23 , ec5 < 23 'fail'        when ec4 < 23 , ec5 < 23 'fail'        else 'pass'    end result (    select * student ) st pivot (     sum(marks) [subject] in (ec1, ec2, ec3, ec4, ec5) ) 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