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.
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 Value1 41 21 42 32 12 8The desired output would be:GroupNo ORValue1 62 11The ORValue for Group 1 is obtained by performing an OR operation on all the values as follows:4 --> 0 1 0 02 --> 0 0 1 04 --> 0 1 0 0ORValue --> 0 1 1 0 which is 6Here is the code to create the table:CREATE TABLE [dbo].[ORTest]( [GroupNo] [int] NOT NULL, [Value] [int] NOT NULL) ON [PRIMARY]GOINSERT 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 Value1 41 21 42 32 12 8The desired output would be:GroupNo ORValue1 62 11 |
|
|
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) |
|
|
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; |
|
|
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. |
|
|
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) |
|
|
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! |
|
|
|
|
|
|
|