CODEWITHSUNDEEP
sql
Melissa
Melissa

Reputation: 83

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

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.

Upvotes: 6

Views: 18729

Answers (5)

SAM
SAM

Reputation: 31

No doubt CASE Statement is a good way to do this, use a User defined function instead of putting CASE statement in the SELECT statement.

CREATE FUNCTION udf_Set_Status 
(
    @ID INT
)
RETURNS NVARCHAR(25)
AS
BEGIN

    DECLARE @Result NVARCHAR(25)

    SELECT @Result =   Case
                       When @ID = 0 then 'Sold Out'
                       When @ID = 1 then 'Available'
                       When @ID = 2 then 'Coming Soon'
                       End
    RETURN @Result

END
GO

Eg:

SELECT ItemId,
       Item_name,
       price,
       Item_status,
       DBO.udf_Set_Status (Item_status) AS [Item_status],
FROM dbo.YourTable

Upvotes: 0

onedaywhen
onedaywhen

Reputation: 57023

There would appear to be a good case for a lookup base table (i.e. 'permanent'). But you can materize one 'on the fly' using a CTE e.g. 

WITH StatusesLookup (status_ID, status_description)
     AS
     (
      SELECT status_ID, CAST(status_description AS VARCHAR(20))
        FROM (
              VALUES (0, 'Sold Out'), 
                     (1, 'Available'), 
                     (2, 'Coming Soon')
             ) AS StatusesLookup (status_ID, status_description)
     )
SELECT T1.Item_ID, T1.Item_Name, T1.Price, 
       T1.Item_Status, S1.status_description AS Description
  FROM YourTable AS T1
       INNER JOIN StatusesLookup AS S1
          ON S1.status_ID = T1.Item_Status;

Upvotes: 1

codingbadger
codingbadger

Reputation: 43984

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]

Upvotes: 7

Marco
Marco

Reputation: 57573

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.

Upvotes: 1

Simon Hughes
Simon Hughes

Reputation: 3574

With Macro's table called ItemStatus (0, 'Sold Out'), (1, 'Available'), (2, 'Coming Soon'), the select would be:

select a.ItemId,
       a.Item_name,
       a.price,
       a.ItemStatus,
       b.Description
From ItemTable a JOIN ItemStatus b
          ON a.ItemStatusId = b.ItemStatusId

Upvotes: 0

Related Questions