Bit conversion

[JBaileys]

We have a goofy implementation of a multiple join.

Table Customers has a bits field that contains a relationship to associations. A customer can have 0, 1 or many associates.

The poor implementation has a customer.associationid = 16 which represents the record with id = 5.
16 (dec) = 10000 (bin)
11 (dec) = 1011 (bin)

I would like to create a sql statement that takes the assocationid and returns the related bits.
16 => 5
11 => 4,2,1
3 => 2,1
6 => 2, 3

 

[rac2]

Here is something to explore.

DECLARE @mask00010 INT
SET @mask00010 = 2
DECLARE @mask10000 INT
SET @mask10000 = 16

DECLARE @A INT
DECLARE @B INT
SET @A = 17
SET @B = 11
SELECT @A&@mask10000, @B&@mask00010
SELECT CASE WHEN @A&@mask10000 = 16 THEN 5 ELSE 0 END
SELECT CASE WHEN @B&@mask00010 = 2 THEN 2 ELSE 0 END