How to print Text values corresponding to numbers in a column in sql server

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,
The requirement is follows:
I have a table called tblactivity,where I have a column as checkedin_flag.
This checkedin_flag column can contain values [0,1,2,4,6].
Now the corresponding text values are as follows:
0->Incomplete
1->Complete
2->Posted
4->Posted
6->Cancelled
Now I need to write a query where the checkedin_flag value will be printed corresponding to their Text value.There is no other table where this number value->Text value relation is maintained.
Can you please let me know how to write the query:
select checkedin_flag as CheckedIn_Status from tblactivity --but I want to display the text values.

Posted 20-Nov-11 21:23pm

avishekrc

Updated 14-Oct-21 2:26am

Add a Solution

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Reiss · Accepted Answer · 2011-11-20T21:46:00

I would strongly suggest that you create a foreign key table to maintain this and use a join query to get the value.

If you can't or don't want to use this approach then you would need to use a case statement

SQL

SELECT 
    'CheckedIn_Status' =
    CASE checkedin_flag
        WHEN 0 THEN 'Incomplete'
        WHEN 1 THEN 'Complete'
        ...
        ELSE 'Unknown Value' -- Referential Integrity is just one reason why a FK table should be used
    END
FROM 
    tblactivity
ORDER BY 
    checkedin_flag;

If you use this approach I would suggest that you make it a function - it would be slower but you would only need to maintain it in one place

Amir Mahfoozi · Accepted Answer · 2011-11-20T21:48:00

Solution 2

Try this :

SQL

select case checkedin_flag when 0 then 'Incomplete' 
when 1 then 'Complete' 
when 2 then 'Posted' 
when 4 then 'Posted' 
when 6 then 'Cancelled' 
else '-'
end CheckedIn_Status from tblactivity

Hope It Helps.

Posted 20-Nov-11 21:48pm

Amir Mahfoozi

OriginalGriff · Accepted Answer · 2011-11-20T21:54:00

Solution 3

Personally, I would create a second table to do this - it is a lot more maintainable.
But, if you can't:

SQL

SELECT *, CASE checkedin_flag 
            WHEN 0 THEN 'Incomplete'
            WHEN 1 THEN 'Complete'
            WHEN 2 THEN 'Posted'
            WHEN 4 THEN 'Posted'
            WHEN 6 THEN 'Cancelled'
            ELSE 'Unknown'
          END
   FROM myTable

Posted 20-Nov-11 21:54pm

OriginalGriff

Comments

OriginalGriff 14-Oct-21 9:03am

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Member 15394066 14-Oct-21 10:04am

Thanks

OriginalGriff 14-Oct-21 10:24am

You're welcome!

How to print Text values corresponding to numbers in a column in sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

How to print Text values corresponding to numbers in a column in sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us