SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Bitwise OR on multiple records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

petersrj
Starting Member

20 Posts

Posted - 05/27/2011 :  15:44:26  Show Profile  Reply with Quote
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 - 05/27/2011 :  16:06:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 05/27/2011 :  16:12:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 05/27/2011 :  16:15:59  Show Profile  Reply with Quote
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 - 05/28/2011 :  19:02:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 05/31/2011 :  18:23:08  Show Profile  Reply with Quote
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 Posts

Posted - 01/20/2014 :  15:43:56  Show Profile  Reply with Quote
Sunitabeck's answer (Posted - 05/27/2011 : 16:15:59) worked great for me!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000