SQL select replace integer with string

Question

Goal is to replace a integer value that is returned in a SQL query with the char value that the number represents. For example:

A table attribute labeled ‘Sport’ is defined as a integer value between 1-4. 1 = Basketball, 2 = Hockey, etc. Below is the database table and then the desired output.

Database Table:

Player     Team     Sport -------------------------- Bob        Blue     1 Roy        Red      3 Sarah      Pink     4  

Desired Outputs:

Player     Team     Sport ------------------------------ Bob        Blue     Basketball Roy        Red      Soccer Sarah      Pink     Kickball 

What is best practice to translate these integer values for String values? Use SQL to translate the values prior to passing to program? Use scripting language to change the value within the program? Change database design?

Answer 1

The database should hold the values and you should perform a join to another table which has that data in it.

So you should have a table which has say a list of people

ID Name FavSport
1 Alex 4
2 Gnats 2

And then another table which has a list of the sports

ID Sport
1 Basketball
2 Football
3 Soccer
4 Kickball

Then you would do a join between these tables

select people.name, sports.sport  from people, sports  where people.favsport = sports.ID 

which would give you back

Name Sport
Alex Kickball
Gnat Football

You could also use a case statement eg. just using the people table from above you could write something like

select name,         case           when favsport = 1 then 'Basketball'           when favsport = 2 then 'Football'           when favsport = 3 then 'Soccer'           else 'Kickball'         end as "Sport"  from people 

But that is certainly not best practice.