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;