Need to Return Multi Records on Table with Multi Bit Fields 3

[smatthews]

Hello,

I have a table called sections that has an id and nine checkboxes (bit fields.) Either 1 or 9 could be = true. I need to return a record with an id and if the checkbox is true then the name of that field. Example:

from this:
id1, section1, section2 (both checkboxes turned on)
id2, section (one checkbox turned on)

to:
id1, section1
id1, section2
id2, section1

Any ideas for the best way to handle this?

Thanks

 

[hmckillop]

If i was doing this i would use a bitmask, it looks ideal for it.

have a look at

http://blogs.conchango.com/jamesrow...7/04/using-a-bitmask-a-practical-example.aspx

This gives a good example of how to use it.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

I might be tempted to use a bitmask for this too. Unfortunately, bitmask's can backfire on you (like when you need to store more than 2 states for each one). If this data was stored in a normalized table, this would be super simple. (a narrow table with an ID and another column to indicate which checkbox was selected.)

Anyway..... to solve this problem, I would probably write a UNION ALL query, where I hard-code the 2nd column. Something like this....

Declare @Temp Table(Id Int, Check1 Bit, Check2 Bit, Check3 Bit)

Insert Into @Temp Values(1, 1, 1, 1)
Insert Into @Temp Values(2, 0, 0, 1)
Insert Into @Temp Values(3, 0, 1, 0)
Insert Into @Temp Values(4, 0, 0, 0)

Select id, '[!]Check1[/!]' As WhichCheck 
From   @Temp
Where  [!]Check1[/!] = 1

Union All 

Select id, '[!]Check2[/!]' As WhichCheck 
From   @Temp
Where  [!]Check2[/!] = 1

Union All 

Select id, '[!]Check3[/!]' As WhichCheck 
From   @Temp
Where  [!]Check3[/!] = 1
Order By Id, WhichCheck

HINT: You can copy/paste the above code to a query window to see how it works. You will, of course, want to repeat each query to that each of your 9 columns are accommodated.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I was going to suggest solution close to what George suggested, but dismissed it as non-elegant.

 

[Emtucifor]

SELECT
   S.Id,
   Section = CASE X.Selector
      WHEN 1 THEN 'Section1'
      WHEN 2 THEN 'Section2'
      WHEN 3 THEN 'Section3'
   END,
   Value = CASE X.Selector
      WHEN 1 THEN Section1
      WHEN 2 THEN Section2
      WHEN 3 THEN Section3
   END
FROM
   Sections S
   CROSS JOIN (
      SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   ) X (Selector)

 

[Emtucifor]

I missed that you only wanted checked items.

SELECT
   S.Id,
   Section = CASE X.Selector
      WHEN 1 THEN 'Section1'
      WHEN 2 THEN 'Section2'
      WHEN 3 THEN 'Section3'
   END
FROM
   Sections S
   CROSS JOIN (
      SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
   ) X (Selector)
WHERE
   CASE X.Selector
      WHEN 1 THEN Section1
      WHEN 2 THEN Section2
      WHEN 3 THEN Section3
   END = 1

Basically the same query.

 

[hmckillop]

There are probably even a few more suggestions on how to do this.
Going back to the bitmask, the main reason why I suggested this was due to each field in original suggestion being of type bit, and thus only 2 options available for each field.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[smatthews]

Thanks to everyone for your ideas. I'm glad I got to see the bitmask idea, I think I will be able to use it in the future. I am going with the union all select from gmnastros.

Thanks Again!

 

[Emtucifor]

George's solution works great, but you should know that it will use one table access per select statement and mine will use one total. If performance matters to you, may I humbly suggest you compare my query?

 

[smatthews]

Emtucifor,

You are so right...thanks for making me take a second look....works great! Have a star.

smatthews

 

[Emtucifor]

You can change the numbers to column names and eliminate a case statement.

 

[smatthews]

Even better...thanks Emtucifor