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

Posted on 2012-08-27
Medium Priority
Last Modified: 2012-08-27
Hello Experts... Novice here.

I have a table that has columns:


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


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
5 = Admin 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
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

Open in new window

LVL 75

Accepted Solution

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

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'
                    WHEN '5' THEN 'Admin 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'
FROM [tablename]

Open in new window

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.

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.

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question