Sanjaya Weerakkody Sanjaya Weerakkody - 1 year ago 75
SQL Question

SQL Convert number to a word

I'm writing a simple SELECT query to get retrieve a set of item numbers from a SQL table.

now from this table, i want to get the Item type which can be one of three.

Make, Buy or undefined

My issue is that this is indicated in a number in the database. My table has a column called
but says
1 or 2 or 3 for Make, Buy or undefined

Could anyone advise me of a way to add a column to my select Query that can show the equivalent wording for Item Type please?

Below is what i'd like to see,

Item_Number Item_Type_Num Item Type_val
A 1 Make
B 2 Buy
C 3 Undefined
D 2 Buy
E 1 Make

This third column is what i'd like to see.

Answer Source

Use simple Case Expression.

    Item_Type_Num ,
    Case Item_Type_Num
       when 1 then 'Make'
       when 2 then 'Buy'
       when 3 then 'Undefined'
    else 'Unknown'
    end as 'Item_type_val'
from Yourtable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download