Help on select query - 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)

Help on select query

Author

Topic

Rajiv_From_India
Starting Member

2 Posts

Posted - 2010-03-20 : 17:00:15

I have a 'bit' type column named 'Status' in one of my table. I will have to query it to get the value from this column such that if Status=0 then show 'In-Active' else if it is 1 then show 'Active'. Please help me on this.

I can do it by writing a procedure in which a temporary intermediate table can be used. But I would like to know if this can directly be done using a simple select statement which might look something as follows

select column1 as 'Col 1', (if (status=0) 'In-Active' else 'Active') as 'Active Status' from table1

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-20 : 17:39:01

SELECT CASE WHEN Status = 1 THEN 'Active' ELSE 'In-Active' END AS ColumnName
FROM table1

You can also use more than one WHEN clause with a CASE statement. Suppose status was an int type, you could do this:

SELECT CASE
WHEN Status = 0 THEN 'Closed'
WHEN Status = 1 THEN 'Open'
WHEN Status = 2 THEN 'Pending'
WHEN Status = 3 THEN 'Complete'
ELSE 'Unknown' END AS ColumnName
FROM xxx

There are 10 types of people in the world, those that understand binary, and those that don't.

Rajiv_From_India
Starting Member

2 Posts

Posted - 2010-03-21 : 09:53:50

Thanks DBA it worked. But what was wrong with the if statement I used. Can you guide me on this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-21 : 10:00:02

you can't use IF statement inside a query. use CASE .. WHEN instead

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources