query:Column name based on bit value

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)

query:Column name based on bit value

Author

Topic

kunal1982
Starting Member

3 Posts

Posted - 2009-03-09 : 05:53:06

I am working on a table which has structure like below: (Column A,B,C) . In each row one of them can be true.

A B C [<-Columns Name]
___________
1 0 0
0 1 0
0 0 1

Now i want to add a 4th column indicating the column for which bit value was true: Eg:

A B C D [<-Columns Name]
___________
1 0 0 A
0 1 0 B
0 0 1 C

Any idea how to go about this...?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 06:14:30

try like this
select *, case when a = 1 then 'A'
when b =1 then 'B'
when c =1 then 'C' end as col4
from tablename

kunal1982
Starting Member

3 Posts

Posted - 2009-03-09 : 06:33:09

thanks it works

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-09 : 06:38:56

quote:
Originally posted by kunal1982

thanks it works

welcome

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-09 : 07:15:02

You could use bklr's case statement in a trigger if you need to keep the column updated automatically:

CREATE TRIGGER TRG_CheckBits
ON [dbo].[A_table]
AFTER INSERT
AS
BEGIN

UPDATE [dbo].[A_table]
SET D = case when A = 1 then 'A'
when B =1 then 'B'
when D =1 then 'C' end

END

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-09 : 07:17:36

Thinking about it - it might be better to have bklr's SELECT inside a View rather than using a trigger - so no need for the overhead of a trigger

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources