sql server - showing values as different values

Question

I have the following situation in Sql Server:

In a table we have a column that we fill with three possible values 1, 2 and 3. Let's suppose that 1 means yes, 2 means no and 3 means maybe.

Is there a way to select this column showing the values as yes, no and maybe instead of 1, 2 and 3?

Even better than using a case expression you could create a lookup table to hold the text value. Then you simply join to it and display the text column. That way if you add another value or whatever you don't have to change any code, just add another value in your table and everything else will just work. — Sean Lange, Nov 20 '17 at 16:01

score 0 · Answer 1 · answered Nov 20 '17 at 15:54

0

Yes ..you can use CASE Expression to do that

select 
case value 
  when 1 then 'yes'
  when 2 then 'no'
  when 3 then 'maybe'
end
from
table

answered Nov 20 '17 at 15:54

TheGameiswar

27,855
8
56
94

score 0 · Answer 2 · answered Nov 20 '17 at 15:55

0

You can use Case for this as shown below

SELECT
CASE test_column
  WHEN 1THEN 'YES' 
  WHEN 2 THEN 'NO'  
  ELSE 'MAY BE' 
END as test_op
FROM table1

answered Nov 20 '17 at 15:55

Ragha Raj

98
4

score 0 · Answer 3 · answered Nov 20 '17 at 15:55

Yes it is possible, you can use case which would be something like this

      select 
      case when field = 1 then 'YES' 
           when field = 2 then 'NO' else 'MAYBE' end FieldName
      from table

Note: any value other than 1 or 2 would be maybe, you can add another case for the number 3.

score 0 · Answer 4 · answered Nov 20 '17 at 16:07

An alternative to the case/when statements seen in other answers is to create a reference table that contains the description of the values (1/2/3).

The biggest advantage of doing it this way is that if this is used in multiple places, you can update all of them at once.

The dbo.curValDesc table is the reference table you'd need to create/populate. Then your consuming queries can look like the one at the bottom.

create table dbo.existingData
    (
        rowID int
        , curVal tinyint --the column with values of 1/2/3
    )

insert into dbo.existingData
values (1, 1)
    , (2, 2)
    , (3, 3)
    , (4, 3)
    , (5, 2)
    , (6, 1)
    , (7, 1)

create table dbo.curValDesc
    (
        curVal tinyint
        , curValDesc varchar(10)
    )

insert into dbo.curValDesc
values (1, 'Yes')
    , (2, 'No')
    , (3, 'Maybe')

select ed.rowID
, cvd.curValDesc
from dbo.existingData as ed
inner join dbo.curValDesc as cvd on ed.curVal = cvd.curVal

sql server - showing values as different values

4 Answers4