CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
mjyazdani
mjyazdani

Reputation: 2035

Get the "OR" result of all rows of a bit column

I have a table like this:

**ID**  | **read** | **edit** | **delete**|

  1         true       false    false

  2         false      false     true 

  3         true       false     true 

  4          true       false     false

I want to "OR" the rows and at last create a row that contain the OR result of them. is there any way to do it without for loop? what is the best way ? (row may be so many and i think for loop may reduce speed)

Upvotes: 2

Views: 331

Answers (2)

shree.pat18
shree.pat18

Reputation: 21757

Try this:

select 
cast(max(cast([read] as int)) as bit) as [overall_read],
cast(max(cast([edit] as int)) as bit) as [overall_edit],
cast(max(cast([delete] as int)) as bit) as [overall_delete]
from tbl

a or b is True when at least 1 of a or b is True, and False otherwise. So you can directly reduce this to getting the maximum value for each column, as @Joachim has also pointed out.

Upvotes: 3

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

You could just cast the bits to integers and use MAX to get the biggest value;

SELECT MAX(CAST([read]   AS INT)) [read],
       MAX(CAST([edit]   AS INT)) [edit], 
       MAX(CAST([delete] AS INT)) [delete]
FROM mytable;

An SQLfiddle to test with.

Upvotes: 5

Related Questions