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 2008 Forums
 Transact-SQL (2008)
 Bitwise OR on multiple records

Author  Topic 

petersrj
Starting Member

20 Posts

Posted - 2011-05-27 : 15:44:26
I have a need to perform a bitwise OR operation on grouped data much the same way the SUM aggregate function returns a single value for grouped data. Here is the input table:

GroupNo Value
1 4
1 2
1 4
2 3
2 1
2 8

The desired output would be:

GroupNo ORValue
1 6
2 11

The ORValue for Group 1 is obtained by performing an OR operation on all the values as follows:

4 --> 0 1 0 0
2 --> 0 0 1 0
4 --> 0 1 0 0
ORValue --> 0 1 1 0 which is 6

Here is the code to create the table:
CREATE TABLE [dbo].[ORTest](
[GroupNo] [int] NOT NULL,
[Value] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO ORTest Values ('1', '4')
INSERT INTO ORTest Values ('1', '2')
INSERT INTO ORTest Values ('1', '4')
INSERT INTO ORTest Values ('2', '3')
INSERT INTO ORTest Values ('2', '1')
INSERT INTO ORTest Values ('2', '8')

Thank you

petersrj
Starting Member

20 Posts

Posted - 2011-05-27 : 16:06:43
Sorry, the formatting looked better before posting. Here is the input and output:

GroupNo Value
1 4
1 2
1 4
2 3
2 1
2 8

The desired output would be:

GroupNo ORValue
1 6
2 11
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-27 : 16:12:43
You can write your own aggregation function using CLR level coding. Look up "CLR User-Defined Aggregates" in BOL for details

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-27 : 16:15:59
Here is "a" way of doing it, which is probably not optimal, but the parser didn't complain, and the results seem right, at least for your test data.

;WITH A AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY GroupNo ORDER BY (SELECT NULL) ) AS rn
FROM OrTest
),
B AS
(
SELECT *
FROM A WHERE rn = 1

UNION ALL

SELECT a.GroupNo, a.Value|b.Value, a.rn
FROM A
INNER JOIN B ON a.rn = b.rn+1 AND a.GroupNo = b.GroupNo
),
C AS
(
SELECT b.GroupNo, b.Value,ROW_NUMBER() OVER (PARTITION BY GroupNo ORDER BY rn DESC) AS rn2
FROM B
)
SELECT GroupNo, VALUE AS ORValue
FROM C
WHERE rn2 = 1;

Go to Top of Page

petersrj
Starting Member

20 Posts

Posted - 2011-05-28 : 19:02:56
Thanks for the input. I went with Bustaz Kool's suggestion and created the aggregate function myself. Other than battling data types, it was pretty painless to implement on my machine.

Thanks for opening my eyes to this approach.

Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-31 : 18:23:08
Mi gusto!

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)
Go to Top of Page

jdobbelaar
Starting Member

1 Post

Posted - 2014-01-20 : 15:43:56
Sunitabeck's answer (Posted - 05/27/2011 : 16:15:59) worked great for me!
Go to Top of Page
   

- Advertisement -