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

Posted on 2012-08-27
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
    LVL 29

    Assisted Solution

    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 74

    Accepted Solution


    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

    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.
    LVL 1

    Author Closing Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now