Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

Working with bitwise data in SQL Server

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;

Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...