add a temporary column in SQL, where the values depend from another column

Question

I have this table:

ID | name | result | 
--------------------    
 1 |  A   |   1    |
--------------------
 2 |  B   |   2    |
--------------------
 3 |  C   |   1    |
--------------------
 1 |  A   |   2    |
--------------------
 4 |  E   |   2    |
--------------------

I want to add a new temporary column next to |result|, and where result=1 the value should be 100, and where result=2 the value should be 80 so it should look like this:

ID | name | result | NewColumn|
-------------------------------    
 1 |  A   |   1    |  100     |
-------------------------------
 2 |  B   |   2    |   80     |
-------------------------------
 3 |  C   |   1    |  100     |
-------------------------------
 1 |  A   |   2    |  80      |
-------------------------------
 4 |  E   |   2    |  80      |
-------------------------------

How can I query this in SQL ?

Answer 1

Use a CASE expression in your SELECT's column list - something like this:

SELECT
    ID,
    name,
    result,
    CASE
        WHEN result = 1 THEN 100
        WHEN result = 2 THEN 80
        ELSE NULL
    END AS NewColumn
FROM YourTable

Add additional WHEN expressions or alter the ELSE expression as needed.

Answer 2

You could add a case statement to your query:

SELECT id, name, result, CASE result WHEN 1 THEN 100 WHEN 2 THEN 80 ELSE NULL END
from   my_table