select + case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select + case

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-27 : 03:33:28

I have a field in my table which its type is int. every digit has meaning for example 1 means the request is accepted, 0 means rejected and etc.

I want to use a select command to show the table to the user but instead of 0,1,2,... the user see the rejected, accepted,... in each row

How should i write this select command? i thought i should use CASE but it didnt work.
please help

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 03:38:33

SELECT CASE COLNAME WHEN 1 THEN 'Accepted' WHEN 0 THEN 'Rejected' ELSE '' END FROM urtable

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 03:39:46

select case when field = 1 then 'accepted'
case when field = 2 then 'rejected'....
end
from urtable

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 03:41:12

Hi there
Hope this will help you.
SELECT field,
CASE
WHEN field = 1 then request is accepted'
WHEN field = 0 THEN 'rejected '
ELSE
''
END AS Status
FROM Table
ORDER BY Table.field

Avantha Siriwardana

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-27 : 03:41:18

hi dear,

You are correct, you must use the CASE only. Here is the sample code try with this. Let me know if you have any issues.

Code Snippet
DECLARE @Employee TABLE(Id int IDENTITY(1,1),Name varchar(50), Address varchar(300),Phone int)
INSERT INTO @Employee
SELECT 'Chirag Darji','Ahmedabad',123456789 UNION ALL
SELECT 'Dipak Patel','USA', 123456789 UNION ALL
SELECT 'Shailesh Patel','USA', 123456789 UNION ALL
SELECT 'Piyush Vadher','Gujarat', 123456789 UNION ALL
SELECT 'Mihir Panchal','Gujarat', 123456789 UNION ALL
SELECT 'Vishal Patel','Ahmedabad', 123456789 UNION ALL
SELECT 'Thiyagarajan','Arcot', 123333389 UNION ALL
SELECT 'SureshBabu','TamilNadu', 12342349

SELECT CASE ID
WHEN 1 THEN 'Accepted'
WHEN 2 THEN 'Rejected'
ELSE 'TODO'
END AS STATUS,* FROM @Employee

I included for 1 and 2 repeat the same other options also.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

Exir
Posting Yak Master

151 Posts

Posted - 2009-01-27 : 03:44:54

then i should join this to another select ? becouse i want all fields be shown to the user

SELECT CASE FIELD WHEN 1 THEN 'Accepted' WHEN 0 THEN 'Rejected' ELSE '' END FROM urtable
union
SELECT filed from tbl

?

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 03:46:46

SELECT *,CASE FIELD WHEN 1 THEN 'Accepted' WHEN 0 THEN 'Rejected' ELSE '' END AS 'Status'FROM urtable

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-27 : 03:49:33

If urtable and tbl are two different tables

SELECT *,CASE FIELD WHEN 1 THEN 'Accepted' WHEN 0 THEN 'Rejected' ELSE '' END AS 'Status'FROM urtable ut
INNER JOIN tbl t ON (ut.id = t.id)

id is the common column from ur tables

Jai Krishna

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-01-27 : 04:16:33

did you get the answer?

Avantha Siriwardana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources