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
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-01 : 04:54:38
ok. thought as much!
Go to Top of Page
   

- Advertisement -