How to change column name and value depending on condition?

Question

I want to change column name and value depending on condition. My table is..

Roll Name Mark 3 Chaity 87 1 Anis 75 4 Unknown 30 2 Badol 0

And I want to get like this

Roll Name Grade 3 Chaity A+ 1 Anis A 4 Unknown F 2 Badol F

Where Mark and Grade mapping is as follows:

0 to 60 is F 61 to 79 is A 80 and above is A+

Community · Accepted Answer · 2021-01-18 12:30:34Z

4

A simple CASE expression will do it:

SELECT Roll, Name, Grade = CASE WHEN Mark <= 60 THEN 'F' WHEN Mark <= 79 THEN 'A' ELSE 'A+' END FROM tbl

11 silver badge

answered Apr 12, 2016 at 2:04

31.9k7 gold badges43 silver badges71 bronze badges

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

1 Comment

I think the second case would be WHEN Mark <80, because 79 is still considered as A

Squirrel · Accepted Answer · 2016-04-12 02:07:05Z

you can use CASE WHEN ... or create a Grade table

SELECT Roll, Name, Grade = case when Mark >= 80 then 'A+' when Mark between 61 and 79 then 'A' else 'F' end FROM yourtable

using a Grade table

SELECT t.Roll, t.Name, g.Grade FROM yourtable t outer apply ( select x.Grade from GradeTable x where t.Mark between x.Mark_from and x.Mark_to ) g

But isn't the grading system a bit drastic ? You either get an A or failed :(