SQL Query - Conditional Values in a User-defined Column

Question

Hi Stack Overflow Community,

I am researching how to create a query that conditionally assigns values in a user-defined column based upon values in another column. I didn't know if this was entirely possible, as I couldn't find any references on this. I know that it's possible to create a user-defined column by just entering in something like 'Yellow' As Color, but these are limited to static values.

I have provided an example of the output below, and the end result would be the user-defined column values would be a string.

   X(Column from Table) Color(User-Defined Column)
   1                     if X = 1, Color = 'Brown'
   2                     if X = 2, Color = 'Blue'
   3                     if X = 3, Color = 'Red'
   4                     if X = 4, Color = 'Orange'
   5                     if X = 5, Color = 'Purple'

 X  Color
 1   Brown
 2   Blue
 3   Red
 4   Orange
 5   Purple

Any input would be greatly appreciated, and thank you in advance!

Daniel

Answer 1

For small amount of available values i think case will be most appropriate.

SELECT X,
CASE
    WHEN X = 1 THEN "Brown"
    WHEN X = 2 THEN "Blue"
    WHEN X = 3 THEN "Red"
    WHEN X = 4 THEN "Orange"
    WHEN X = 5 THEN "Purple"
    ELSE "No color"
END AS Color
FROM Table;