Aggregate bitwise operation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Aggregate bitwise operation

Author

Topic

nirnir2
Starting Member

20 Posts

Posted - 2015-02-26 : 11:07:57

How can I perform bitwise 'or' operation on table records
like sum() but perform OR on all values

create table #tmp1 (FLAGS INT )

insert into #tmp1 values (1 )
insert into #tmp1 values (1 )
insert into #tmp1 values (2 )
insert into #tmp1 values (2 )
insert into #tmp1 values (2 )
insert into #tmp1 values (3 )
SELECT bitsOR(FLAGS) FROM #tmp1

should return 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-26 : 15:48:02

[code]DECLARE @Sample TABLE
(
Flags INT NOT NULL
);

INSERT @Sample
(
Flags
)
VALUES (1),
(1),
(2),
(2),
(2),
(3);

-- SwePeso
WITH cteBits(Flags, Bits, Mask)
AS (
SELECT DISTINCT CAST(Flags AS BIGINT) AS Flags,
CAST(Flags % 2 AS BIT) AS Bits,
CAST(1 AS BIGINT) AS Mask
FROM @Sample

UNION ALL

SELECT CAST(Flags AS BIGINT),
CAST((Flags / Mask / 2) % 2 AS BIT),
CAST(2 * Mask AS BIGINT)
FROM cteBits
WHERE Mask < Flags
)
SELECT SUM(DISTINCT Mask) AS BitsOr
FROM cteBits
WHERE Bits >= 1;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

nirnir2
Starting Member

20 Posts

Posted - 2015-03-01 : 02:22:15

Thanks ,
I'm trying to figure how efficient is it and what is the best practice to get the needed results .

I have a table with few millions of records,
one million distinct clientID
each client has 5-50 records with flags

eventually I need for each client one record with its 'OR'ed clientFlags .

create table #tmp1 (clientID int, clientFlags INT )

insert into #tmp1 values (1,1 )
insert into #tmp1 values (1,1 )
insert into #tmp1 values (1,3 )
insert into #tmp1 values (1,2 )
insert into #tmp1 values (2,3 )
insert into #tmp1 values (3,4 )
insert into #tmp1 values (3,2 )

result should be
1,3
2,3
3,6

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-01 : 03:09:25

pity you didn't post the entire problem the first time. I'm still wondering if there is more to it. You see, the idea of a table with nothing more than a client id and some flags and then having multiple rows per client seems like an extraordinarily bad design.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-01 : 04:06:01

[code]-- SwePeso
WITH cteBits(ClientID, Flags, Bits, Mask)
AS (
SELECT DISTINCT ClientID,
CAST(Flags AS BIGINT) AS Flags,
CAST(Flags % 2 AS BIT) AS Bits,
CAST(1 AS BIGINT) AS Mask
FROM @Sample

UNION ALL

SELECT ClientID,
CAST(Flags AS BIGINT),
CAST((Flags / Mask / 2) % 2 AS BIT),
CAST(2 * Mask AS BIGINT)
FROM cteBits
WHERE Mask < Flags
)
SELECT ClientID,
SUM(DISTINCT CASE WHEN Bits = 0 THEN 0 ELSE Mask END) AS BitsOr
FROM cteBits
GROUP BY ClientID;[/code]For millions of rows or more, the solution would probably be best written in SQLCLR as a T-SQL function.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-01 : 04:19:46

here's a simple way. I did it with tinyint but you can easily extend it to int or bigint


declare @1 table (id int,flags tinyint)
insert into @1(id, flags) values
(0,1),(0,2),(1,3),(1,4),(1,5)

select max(flags & 1) | max(flags & 2) | max(flags & 4) | max(flags & 8) | max(flags & 16) | max(flags & 32) | max(flags & 64) | max(flags & 128) as flags_ored 
from @1
group by id

nirnir2
Starting Member

20 Posts

Posted - 2015-03-01 : 04:43:37

Thank you both .
gbritton, the table has more stuff , which is not relevant for the flags

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-01 : 04:54:38

ok. thought as much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources