2

I must use bitwise operations in a group by query but I didn't found anything.

Table:

PermissionId, BitMask(BigInt)
1,            4
2,            7
1,            8
1,            5

I want results as:

1,           13
2,           7

How can I write this script in T-SQL as below

SELECT PermissionId, BIT_OR(BitMask)
FROM table
GROUP BY PermissionId
Dale K
  • 25,246
  • 15
  • 42
  • 71
Nusret Parlak
  • 83
  • 8

1 Answers1

2

Your question just became very interesting.

Create this function(you may want to reconsider the name)

CREATE function f_test
(
  @param bigint
)
  returns @t table (value bigint)
AS
BEGIN

;WITH CTE AS
(
  SELECT
    @param % 2 value, 
    1 multiplier, 
    @param / 2 remain
  UNION ALL
  SELECT
    remain % 2, 
    multiplier * 2,
    remain / 2
  FROM
    CTE
  WHERE
    remain > 0
)
INSERT @t
SELECT multiplier 
FROM CTE
WHERE value = 1
RETURN

END

Now you can run this script, I am using a table variable, replace it with your table:

DECLARE @t table(PermissionId int, BitMask bigint)
INSERT  @t values(1,4),(2,7),(1,8),(1,5)

SELECT
  t1.PermissionId, 
  SUM(distinct t2.Value) Total
FROM
  @t t1
CROSS APPLY
f_test(t1.BitMask) t2
GROUP BY
  t1.PermissionId

Result:

PermissionId  Total
1             13
2             7
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • If anyone needs to handle cases where the bitmask value may be `0`, then the `CROSS APPLY` must be replaced with `OUTER APPLY` and the sum must be wrapped in ISNULL: `ISNULL(SUM(distinct t2.Value), 0) Total`. – Alexandre Dec 07 '21 at 00:57