I was working with a group of students on a design brief. The brief was to partially implement a database design based on the LinkedIn web site. A discussion came up around the possibility of implementing the contact preferences (essentially a set of boolean values) as bitwise data, so I went away and had a look at some queries for manipulating the data.

To start with here is the DDL for this module:

CREATE TABLE Member
(
      id            INT
    , name          VARCHAR(20)
    , contact_preferences    INT
);
INSERT INTO Member
VALUES (1,'Bob', 255),(2,'John', 84),(3,'Cathy', 73),(4,'Steve', 170);
CREATE TABLE opportunitiesLookup
(
      bitwise int
    , value   varchar(30)
);
INSERT INTO opportunitiesLookup
VALUES (1, 'Career opportunities')
,(2, 'Consulting offers')
,(4, 'New ventures')
,(8, 'Job inquiries')
,(16, 'Expertise requests')
,(32, 'Business deals')
,(64, 'Personal reference requests')
,(128, 'Requests to reconnect');

As you can see, I’ve created a (very cut down) members table with a contact_preferences column which is the bitwise data and a static lookup table which maps the first 8 powers of 2 (2^0 – 2^7) to the various contact options.

Joining the two tables with the SQL Server bitwise & operator (logical AND) we get a table associating a user with their preferences:

Select m.name, o.value
From Member m
Cross Join opportunitiesLookup o
Where m.contact_preferences & o.bitwise <> 0
Order By m.name
    , o.value

It’s pretty straight forward to use this query in a common table expression or sub-query to then filter for the desired results. (You need to remove the order by clause first or this will error)

with assoc as
(
Select m.name, o.value
From Member m
Cross Join opportunitiesLookup o
Where m.contact_preferences & o.bitwise <> 0
)
select name from assoc where value = 'Consulting offers'

There are a number of surprisingly non-trivial ways to produce a list of members’ names followed by a concatenated string of their preferences. The developer at work suggested the following query (with a couple of minor tweaks from me):

SELECT n.name as 'Name', LTRIM(STUFF(
       (SELECT  
              ', ' + o.value AS [text()]
              FROM 
              Member m
              CROSS JOIN opportunitiesLookup o
              WHERE 
              m.contact_preferences & o.bitwise <> 0
              AND m.name = n.name
              ORDER BY 
              m.name
              ,o.value
       FOR XML PATH('')),1,1,'')) AS 'Preferences'
FROM 
       Member n
ORDER BY 
       n.name

Another approach because the options are a static list we can use a pivot to create an intermediary table with the various options broken out into columns, with a 1 if the user has selected this option, 0 if not:

SELECT name
,[Career opportunities]
,[Consulting offers]
,[New ventures]
,[Job inquiries]
,[Expertise requests]
,[Business deals]
,[Personal reference requests]
,[Requests to reconnect]
FROM
(Select m.name, o.value, 1 as num
From Member m
Cross Join opportunitiesLookup o
Where m.contact_preferences & o.bitwise <> 0) AS D
PIVOT(count(num) for value in ([Career opportunities]
,[Consulting offers]
,[New ventures]
,[Job inquiries]
,[Expertise requests]
,[Business deals]
,[Personal reference requests]
,[Requests to reconnect])) AS P;