Geek Answers Handbook

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

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

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

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

where according to the status of the position ... Let "2" represent "Coming Soon",

"1" means "Available,"

while "0" means "Sold"

I want to show the information so that I can say that the user who is viewing the output table knows the status in a more acceptable output (row), and does not look 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 Description is a temporary column that I would like to display as additional information.

, GO?

, . .

+5
5

, - CASE - , 3 ?

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

, .

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

temp

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

[description], Convert

       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]
+7

descr (id, string), :
(0, "" ), (1, "" ), (2, "" )
...

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

CASE...

: , enum, , db; , , ( ) , ... .

+1

, (.. "" ). " ", CTE, .

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;
+1

ItemStatus (0, 'Sold Out'), (1, 'Available'), (2, 'Coming Soon'), :

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

Without a doubt, a CASE Statement is a good way to do this, using a user-defined function instead of putting a CASE statement in a 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

For instance:

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

More articles:


All Articles