Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example:

DECLARE @T TABLE (id smallint identity, BitMask tinyint);
INSERT INTO @T (BitMask) VALUES
  (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT
  t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
OUTER APPLY (
  SELECT * FROM (VALUES
    (t.id, 0, 'Can X'),
    (t.id, 1, 'Can Y'),
    (t.id, 2, 'Can Z')
  ) bm(id, BitNum, Permission)
  WHERE t.BitMask & POWER(2, bm.BitNum) <> 0
) bm

This returns the following info:

id     BitMask BitNum      Permission
------ ------- ----------- ----------
1      0       NULL        NULL
2      1       0           Can X
3      2       1           Can Y
4      3       0           Can X
4      3       1           Can Y
5      4       2           Can Z
6      5       0           Can X
6      5       2           Can Z
7      6       1           Can Y
7      6       2           Can Z
8      7       0           Can X
8      7       1           Can Y
8      7       2           Can Z
9      8       NULL        NULL
10     9       0           Can X

(15 row(s) affected)

So far so good. The trouble comes when I try to do an aggregate by id, so that I have all the permissions in one field. I've tried adding the following APPLY clause to do a standard XML list-string-agg, but I get an error Invalid object name 'bm'.:

OUTER APPLY (
  SELECT 
   ParamList = STUFF(
     (
       SELECT  '; ' + a.Permission
       FROM bm a WHERE a.id = b.id
       ORDER BY a.BitNum
       FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
     ), 1, 2, ''
   )
  FROM bm b
  GROUP BY b.id
) q

Any ideas?

First, we need to make a few minor adjustments to the original code:

• It does not make sense to have a permission value of 0 since 0 means "no permissions".

• The "BitNum" is not the direct value that you use in the POWER function. If you need a "bit" value of 1, that comes from raising 2 to the power of 0. So you need to subtract 1 from the "BitNum" for use in the POWER function.

With those two changes in mind, the following changes to the original query give you the correct initial result set:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

SELECT t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
OUTER APPLY (
SELECT * FROM (VALUES
(1, 'Can Y'),
(2, 'Can Z')
) bm(BitNum, Permission)
WHERE t.BitMask & POWER(2, bm.BitNum - 1) <> 0
) bm


And that query can be further reduced / simplified to be a simple LEFT JOIN as follows:

SELECT t.id, t.BitMask, bm.BitNum, bm.Permission
FROM @T t
LEFT JOIN (VALUES
(1, 'Can Y'),
(2, 'Can Z')
) bm(BitNum, Permission)
ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0


Results (12 rows):

id BitMask BitNum Permission
1 0 NULL NULL
2 1 1 Can Y
3 2 2 Can Z
4 3 1 Can Y
4 3 2 Can Z
5 4 NULL NULL
6 5 1 Can Y
7 6 2 Can Z
8 7 1 Can Y
8 7 2 Can Z
9 8 NULL NULL
10 9 1 Can Y


Next, now that we have the proper base query, you cannot simply add an APPLY since your original query had each permission as a separate row but now you want them grouped into a single row per "BitMask". Hence you need to restructure the request to be the following (or something similar):

SELECT t.id, t.BitMask, PermissionList =
(
SELECT PermissionList = STUFF(
(
SELECT '; ' + bm.Permission
FROM (VALUES
(1, 'Can Y'),
(2, 'Can Z')
) bm(BitNum, Permission)
WHERE t.BitMask & POWER(2, bm.BitNum - 1) <> 0
ORDER BY bm.BitNum
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
)
)
FROM @T t
GROUP BY t.id, t.BitMask;


Results (10 rows):

id BitMask PermissionList
1 0 NULL
2 1 Can Y
3 2 Can Z
4 3 Can Y; Can Z
5 4 NULL
6 5 Can Y
7 6 Can Z
8 7 Can Y; Can Z
9 8 NULL
10 9 Can Y


You also have the option of using SQLCLR to create a User-Defined Aggregate (UDA) that can do this type of String.Join() operation. And an Aggregate function such as this already exists in the SQL# library (which I am the author of, but this function is available in the Free version), though it is hard-coded to use a comma (and no spaces) as the delimiter, and returns an empty string instead of NULL if there are no matches. But, it does make for a much more readable query:

SELECT t.id, t.BitMask, SQL#.Agg_Join(bm.Permission) AS [PermissionList]
FROM @T t
LEFT JOIN (VALUES
(1, 'Can Y'),
(2, 'Can Z')
) bm(BitNum, Permission)
ON t.BitMask & POWER(2, bm.BitNum - 1) <> 0
GROUP BY t.id, t.BitMask;


This is not to say that using a SQLCLR UDA is necessarily the better choice, I am just pointing out that it is a choice, and depending on the specific requirements, might be better.

OR, starting in SQL Server 2017, there is a built-in aggregate function, STRING_AGG, that can handle this.

A slightly different way of testing the Bit value against the Bitmask is to compare the bit-wise AND operation of them to the Bit value itself instead of against <> 0:

DECLARE @T TABLE (id SMALLINT IDENTITY(1, 1), BitMask TINYINT);
INSERT INTO @T (BitMask) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- based on "improved" query
SELECT t.id, t.BitMask, [PermissionList] =
(
SELECT [PermissionList] = STUFF(
(
SELECT '; ' + bm.Permission
FROM (VALUES
(0, 'Default'),
(1, 'Can Y'),
(2, 'Can Z')
) bm(BitNum, Permission)
WHERE t.BitMask & POWER(2, bm.BitNum - 1) = POWER(2, bm.BitNum - 1)
ORDER BY bm.BitNum
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''
)
)
FROM @T t
GROUP BY t.id, t.BitMask;


This gets you a little closer to using 0 as a value, but then you have the problem of that value being implicitly in all records. The results of the above (notice that the ON condition was changed AND I added the 0 record back in):

`id BitMask PermissionList
1 0 Default
2 1 Default; Can Y
3 2 Default; Can Z
4 3 Default; Can Y; Can Z
5 4 Default
6 5 Default; Can Y
7 6 Default; Can Z
8 7 Default; Can Y; Can Z
9 8 Default
10

StackExchange Database Administrators Q#102994, answer score: 4