Solved

# Query to show 'text' in results rather than a numeric value

Posted on 2012-08-27
Medium Priority
521 Views
Hello Experts... Novice here.

I have a table that has columns:

UserID
EA_Level

where EA_Level is made up of two numeric values (##).

Example:

User ID | EA_Level
1 | 11
2 | 34
3 | 55

The first number in the second column represents:

1 = New User
2 = Alpha User
3 = Beta User
4 = Normal User

The second number in the second column represents:

1 = < 24 Hours
2 = 1 Day
3 = 1 Week
4 = 1 Month
5 = 3 Months

The definition above aren't in any tables.

How can I write a query that will display results like this?

User ID | EA_Level
1 | New User < 24 Hours
2 | Beta User 1 Month
3 | Admin User 3 Months
0
Question by:trixits
• 2

LVL 29

Assisted Solution

IrogSinta earned 400 total points
ID: 38339235
Try this query:
``````Select UserID, Choose(Left(EA_Level,1), "New User ","Alpha User ","Beta User ","Normal User ","Admin User ") & Choose(Right(EA_Level,1),"< 24 Hours","1 Day","1 Week","1 Month","3 Months") As EaLevel From NameOfTable
``````
0

LVL 75

Accepted Solution

käµfm³d   👽 earned 1600 total points
ID: 38339243
Try:

``````SELECT user_id, CASE SUBSTR(ea_level, 1, 1)
WHEN '1' THEN 'New User'
WHEN '2' THEN 'Alpha User'
WHEN '3' THEN 'Beta User'
WHEN '4' THEN 'Normal User'
ELSE 'N/A'
END +
CASE SUBSTR(ea_level, 2, 1)
WHEN '1' THEN ' < 24 Hours'
WHEN '2' THEN ' 1 Day'
WHEN '3' THEN ' 1 Week'
WHEN '4' THEN ' 1 Month'
WHEN '5' THEN ' 3 Months'
ELSE 'N/A'
END
FROM [tablename]
``````
0

LVL 29

Expert Comment

ID: 38339255
Oops, I thought this was in Access.

Edit... Then again, SQL Server 2012 did introduce the Choose function so if you have that, then the code I gave would work.
0

LVL 1

Author Closing Comment

ID: 38339344
Thank you, kaufmed, it works.  I just modified the code a bit (replaced the "+" with a comma - decided to put it in two separate columns) and it works perfectly.

I'm not working in Access or SQL Server 2012, IrogSinta, but thank you for the prompt response.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
###### Suggested Courses
Course of the Month14 days, 8 hours left to enroll