How to change / convert values in Output that comes from SQL Server table

Question

I have created a view in my SQL Server database which will give me number of columns.

One of the column heading is Priority and the values in this column are Low, Medium, High and Immediate.

When I execute this view, the result is returned perfectly like below. I want to change or assign values for these priorities. For example: instead of Low I should get 4, instead of Medium I should get 3, for High it should be 2 and for Immediate it should be 1.

What should I do to achieve this?

Ticket#   Priority
123       Low
1254      Low
5478      Medium
4585      High
etc.,     etc.,

Community · Accepted Answer · 2017-05-23 12:22:26Z

2

Use CASE:

Instead of Low I should get 4, instead of Medium I should get 3, for High it should be 2 and for Immediate it should be 1

SELECT 
  [Ticket#],
  [Priority] = CASE Priority
                 WHEN 'Low'       THEN 4
                 WHEN 'Medium'    THEN 3
                 WHEN 'High'      THEN 2
                 WHEN 'Immediate' THEN 1
                 ELSE NULL
               END
FROM table_name;

EDIT:

If you use dictionary table like in George Botros Solution you need to remember about:

1) Maintaining and storing dictionary table

2) Adding UNIUQE index to Priority.Name to avoid duplicates like:

Priority table
--------------------
Id  |  Name | Value
--------------------
1   |  Low  | 4
2   |  Low  | 4
...

3) Instead of INNER JOIN defensively you ought to use LEFT JOIN to get all results even if there is no corresponding value in dictionary table.

edited May 23, 2017 at 12:22

CommunityBot

11 silver badge

answered Sep 20, 2015 at 12:33

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Clay Over a year ago

While casing works, joining a separate table, as in @GeorgeBotros solution is often preferable, because revisiting the definitive source later doesn't require you to go find all your case statements and adjust.

Lukasz Szozda Over a year ago

@Clay As always it depends. 1) Using separate table I need to store it somewhere and maintain it 2) Make sure that Priority.Name is UNIQUE 3) What if there is no corresponding value (LEFT JOIN needed)

Clay Over a year ago

agreed on all points - I had hoped you would elaborate. It serves everybody's interests.

George Botros · Accepted Answer · 2015-09-20 12:54:56Z

1

I have an alternative solution for your problem by creating a new Priority table (Id, Name, Value) by joining to this table you will be able to select the value column

SELECT Ticket.*, Priority.Value 
FROM Ticket INNER JOIN Priority 
ON Priority.Name = Ticket.Priority

Note: although using the case keyword is the most straight forward solution for this problem
this solution may be useful if you will need this priority value in many places at your system

answered Sep 20, 2015 at 12:54

George Botros

4,4293 gold badges31 silver badges50 bronze badges

Collectives™ on Stack Overflow

How to change / convert values in Output that comes from SQL Server table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related