Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

SQL - turn a integer value to a string

If I have a flag has a value (0, 1, 2, 3),  I will like to turn into a corresponding label (beginner, basic, intermediate and advanced)

Table 1
Person|SkillLevel
P1 | 0
P2 | 1
P3 | 2
P4 | 3

Like to get the result
P1 | beginner
P2 | basic
P3 | intermediate
P4 | advanced

How do I write such a sql statement
0
tommym121
Asked:
tommym121
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select case when skillLevel = 0 then 'Beginner'
                  when skillLevel = 1 then  'Basic'
                   --repeat for other 2.
            END as SkillLevelText

from <table>


If you're going to be using this all over the place, create a scalar function, then you could call like:


select fnGetNameBySkillLevel(skillLevel)
from <table>
0
 
OklahomaDaveCommented:
A SQL purist designer would probably at least suggest a lookup table that maps the symbolic values to their literal values, then queries perform a JOIN from Table1 to the lookup table, eg

assuming values are in LookupTable,

select a.person, b.SkillLevelText
  from Table a
  join Lookuptable b
     on a.SkillLevel = b.SkillLevel
0
 
tommym121Author Commented:
Thanks
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now