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 &amp; POWER(2, bm.BitNum) &lt;&gt; 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 &amp; POWER(2, bm.BitNum - 1) &lt;&gt; 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 &amp; POWER(2, bm.BitNum - 1) &lt;&gt; 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 &amp; POWER(2, bm.BitNum - 1) &lt;&gt; 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 &amp; POWER(2, bm.BitNum - 1) &lt;&gt; 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 &lt;&gt; 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 &amp; 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  9           Default; Can Y