How can I OR all the values in a particular column across multiple rows?

[Ken Varn]

Is there any way that I can run an ADO.NET query in SQLServer to do a
bitwise OR operation on a particular column for multiple rows?

i.e., Select OR(ColumnName) from Table

--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------

 

[Guest]

Ken:

Here is a Where clause that I use to select multiple rows based on a mask
(int value) passed in:

WHERE (@pRecStatusMask & RecStatus)>0

So, if the mask has a value of 7, any rows with a RecStatus of 1,2,3,4,5,6
or 7 will get selected.

I believe this is what you needed?

 

[Frans Bouma [C# MVP]]

Ken:

Here is a Where clause that I use to select multiple rows based on a mask
(int value) passed in:

WHERE (@pRecStatusMask & RecStatus)>0

So, if the mask has a value of 7, any rows with a RecStatus of 1,2,3,4,5,6
or 7 will get selected.

I believe this is what you needed?

No I think he needs the value of 7 to be returned by the select if
there are 3 records, with value 1, 2 and 4. I toyed with the idea of
suggesting a query which uses an aggregate (SUM()) but I thought that
that isn't the answer, as the values might not be just flags but could
have more bits set, which then would result in bad values.

So I think it can't be done in 1 query, but perhaps some sql wiz knows
how to solve it

FB

:

--

 

[Guest]

Yes, Frans you're right. I overlooked his example (i.e., Select
OR(ColumnName) from Table). However, I don't see the value of a function
like this, particularly on integer type columns. Assuming a column type of
tinyint that can have random values of 0 to 255 and some large number of
rows, this function will tend to always return 255.
I guess I'm still missing what Ken is asking.

 

[Adrian Parker]

Ken,

As long as the column you're checking is an integer, you can do the
following..

Select * From <Table> Where <ColumnName> | <MaskValue> = <MaskValue>

e.g. if you have 8 rows with numbers 1 thru 8, then a mask of 7 would return
rows 1 to 7, but not 8.

HTH
Adrian Parker

 

[Ken Varn]

No I think he needs the value of 7 to be returned by the select if

there are 3 records, with value 1, 2 and 4. I toyed with the idea of
suggesting a query which uses an aggregate (SUM()) but I thought that
that isn't the answer, as the values might not be just flags but could
have more bits set, which then would result in bad values.

So I think it can't be done in 1 query, but perhaps some sql wiz knows
how to solve it

Your are right on what I am looking for. I want an equivalent SUM operation
but doing a bitwise OR instead.

I was directed to this query that seems to do what I am looking for. Seems
like a lot of work. I wish there was just a simple keyword like SUM to do
it, but the results are what I want. Not sure how good this is when it
comes to performance.

SELECT SUM(Distinct Value & 1) + SUM(Distinct Value & 2) + SUM(Distinct
Value & 4) + SUM(Distinct Value & 8) -- Take this out to bits neeeed

FROM tbl


--
-----------------------------------
Ken Varn
Senior Software Engineer
Diebold Inc.

EmailID = varnk
Domain = Diebold.com
-----------------------------------