SQL output: Is it possible to create a temporary output column?

Question

For example, I have a table as below in my database:

| Item ID | Item Name | Price | Item Status |

where Item ID = int, Item Name = string, Price = int, Item Status = Enum

where as for Item status... Let's say "2" represents "Coming Soon",

"1" represents "Available",

while "0" represents "Sold Out"

I want to show the information such that I can tell the user who views the output table knows the status in a more acceptable output (string) rather than looking at the Enum values:

| Item ID | Item Name | Price         | Item Status | **Description** |
| 123     | Apple     | [some number] | 0           |   Sold Out      |
| 234     | Orange    | [some number] | 2           |   Coming Soon   |

where the Description is the temporary column I would like to display as an additional information.

May I know how the syntax goes in one GO?

Please guide me. Thank you so much in advance.

Answer 1

Well the easiest way to do this would be to use a CASE statement - providing that you do only have 3 descriptions?

select ItemId,
       Item_name,
       price,
       Item_status,
       Case
       When Item_status = 0 then 'Sold Out'
       When Item_status = 1 then 'Available'
       When Item_status = 2 then 'Coming Soon'
       End as [Description]

From dbo.YourTable

Another option if to create a temporary table and join on to that.

Create Table #TempEnums
(
Id int,
Desc varchar(50)
)
Insert Into #TempEnums
Select 0, 'Sold Out' Union Select 1, 'Available' Union Select 2, 'Coming Soon'

Then simply join on to the temp table

select a.ItemId,
       a.Item_name,
       a.price,
       a.Item_status,
       b.Desc as [Description]

From dbo.YourTable a
Join #TempEnums b on a.Item_Status = b.Id

EDIT

To change the datatype of the [description] column just wrap in a Convert statement

       Convert(Varchar(25),  
       Case
       When Item_status = 0 then 'Sold Out'
       When Item_status = 1 then 'Available'
       When Item_status = 2 then 'Coming Soon'
       End) as [Description]

Answer 2

You could create a table descr (id,string) in which you put these records:
(0, 'Sold Out'), (1, 'Available'), (2, 'Coming Soon')
and then join tables during output...

SELECT table.*,descr.description FROM table INNER JOIN descr
ON table.enum_col = descr.id

Or you could use CASE command...

EDITED: I prefer the first solution, because if in future you have to add another enum value, you can do it just working on db; using case you should change your query that could be hard-written (and compiled) in your software and so you have to recompile... and sometimes it's a problem.