How do I alias particular values in SQL?

Question

I will present a question about 'aliasing' values from a column. I will use days of the week as an intuitive example to get my question across, but I am not asking for datetime conversions.

Suppose I have the following SQL script:

SELECT DaysOfWeek 
FROM [databasename].[dbo].[tablename]

Now, the column DaysOfWeek will return string values of the days' names, i.e. "Monday," "Tuesday," and so forth.

What if I wanted the query to return the integer 1 for 'Monday', 2 for 'Tuesday', and so forth? I would want to assign a particular value to each of the week's days in the SELECT statement, but I'm not sure how to go about doing that.

I'm relatively new to SQL, so I just thought I'd ask for an intuitive method to perform such a task.

Edited to add: I'm only using days of the week and their respective integer representation as an easy example; my task does not involve days of the week, but rather employee code numbers and corresponding titles.

Answer 1

You can do this using case:

SELECT (CASE DaysOfWeek 
             WHEN 'Monday' THEN 1
             WHEN 'Tuesday' THEN 2
             . . .
        END)

Under most circumstances, it is unnecessary to store the day of the week like this. You can readily use a function, datepart() or datename(), to extract the day of the week from a date/time value.

If the column is in a table, and not part of a date, then you might want to include the above logic as a computed column:

alter table t add DayOfWeekNumber as (case DaysOfWeek when 'Monday' then 1 . . .);

Answer 2

Use CASE, here you have the definition and one example :

select
    CASE 
          WHEN(DaysOfWeek="Monday") THEN 1
          WHEN(DaysOfWeek="Thusday") THEN 2
          ....
    ELSE  -1 
from table

Hope this help!