get the name of the bit column set to 1

Question

Post reply

get the name of the bit column set to 1

dubem1-878067

Ten Centuries

Points: 1044
More actions
November 29, 2010 at 1:42 pm

#231940

Hello
In a table I have 10 bit columns and I know only one of the 10 will be to 1, the 9 others will have 0.
I would like a query returning for each row the name of the column where the bit is 1.
is it something achievable?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

select

case

when col1 = 1 then 'col1'

when col2 = 1 then 'col2'

when col3 = 1 then 'col3'

... and so on ...

else '' end

from

MyTable

Lowell SSC Guru Points: 323504 More actions · Answer 2

==edit== too slow again....

it'll have to be a case statement i think:

SELECT

CASE

WHEN col01 = 1 THEN 'col1'

WHEN col02 = 1 THEN 'col2'

WHEN col03 = 1 THEN 'col3'

WHEN col04 = 1 THEN 'col4'

ELSE 'All Zeros/No Selection'

END

FROM SOMETABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Design question: Why would you use this approach?

If each of the ten indicators are mutually exclusive, they should be listed in a single field as a status flag or something. Ten bit fields indicate ten unique statuses.

Now, is there a way to determine which is on? Well... kinda. There are some workarounds to dealing with this. My initial approach (which seem simple to me, but depends on how much you work with them...) would be to turn them into a bitmask via contatonation and then test against 10 bitmask settings to see which to activate.

Another would be to create some godawful case statement to create an 11th column during selection to create the above mentioned status flag.

You're pretty much building workarounds here.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

Michael and Lowell nailed it, that's what you need to do.

You might want to consider adding that as a computed column to the table, rather than doing it in the SELECT.

For example:

ALTER TABLE tablename

ADD columnIsOn AS

CASE WHEN col1 = 1 THEN 'col1' ...

Then you can do this:

SELECT ..., columnIsOn, ...

FROM tablename

ORDER BY columnIsOn

Scott Pletcher, SQL Server MVP 2008-2010