Hi All,

If I have the following one-column table:

Grade
80
100
90
60
50
90

I want to add a new column (in the result, not in the physical table)
called "Letter_Grade" using the following convertion method:

50 = F
60 = D
70 = C
80 = B
90 and 100 = A

How can I do it?

You may provide more than one solutions. Thanks.

SQL Learner

You can use a CASE expression:

SELECT grade,
CASE WHEN grade = 50 THEN 'F'
WHEN grade = 60 THEN 'D'
WHEN grade = 70 THEN 'C'
WHEN grade = 80 THEN 'B'
WHEN grade IN (90, 100) THEN 'A'
END AS letter_grade
FROM Grades;

--
Plamen Ratchev
http://www.SQLStudio.com

Plamen,

Thank you so much again! That works.

SQL Learner

SQL Learner (excelmodeling (AT) gmail (DOT) com) wrote:
: Plamen,

: Thank you so much again! That works.

: SQL Learner

Are you sure you correctly stated the original problem? I also wonder if
there was a reason the original values were stored in a table.

I, too, was concerned about that. If this were to be used as a means
of transforming any score to the correct grade, then the CASE approach
shown would have to specify all possible expected score values. If
that is the case, then the CASE statement should be more generalized
so as to map a range of scores to a letter grade.

RRR wrote:
In this case a CASE expression would not be my first choice. My first
choice would be to create a table (in case one doesn't already exist)
with a mapping between the different grade types (or range of grade
types).

In its simplest form:

CREATE TABLE grades
(grade_number tinyint NOT NULL PRIMARY KEY
,grade_letter char(1) NOT NULL
)
INSERT INTO grades VALUES (50,'F')
INSERT INTO grades VALUES (60,'D')
INSERT INTO grades VALUES (70,'C')
INSERT INTO grades VALUES (80,'B')
INSERT INTO grades VALUES (90,'A')
INSERT INTO grades VALUES (100,'A')

SELECT ..., grades.grade_letter
FROM my_table
JOIN grades
ON grades.grade_number = my_table.grade

I think that for this type of applications, the solution with a CASE
expression should be reserved for ad-hoc selections, which I don't think
this is.

--
Gert-Jan

Thank you for your feedback and help, guys.

The one-column table was used only for the purpose of simplication.
My objective was to know how to get a second column which contains the
converted values from the first column. The application can be used
for many occuations. For one, we can use it to convert, say "NY",
"CA" .... to "New York", "California"...

What Plamen provided was enough for the purpose.

I think in term of efficient, Gert-Jan's method may be the best if I
have a lot of data to "convert".

SQL Server

SQL Learner wrote:
IMO, data should be in a database, not in a query. The example you give
in this post is a typical example of a states table.

What I would like to add is that you don't want to have the same CASE
expression in multiple places, because that makes it very hard to
maintain. So I would advise against creating lookup tables in
expressions. On the other hand, a CASE expression can be a great
solution for data formatting or adding default descriptions.

--
Gert-Jan