Clever Geek Handbook

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" a string and finally create a string containing the OR result. is there a way to do this without a loop? What's the best way? (there can be so many lines and I think the loop might decrease the speed)

+3


source to share


2 answers


Try the following:

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

True if at least 1 of a

or b

is True, False otherwise. So you can directly decrease this value to get the maximum value for each column, as @Joachim also pointed out.

+4


source


You can simply write the bits into integers and use MAX

to get the largest value;

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


SQLfiddle for testing with .

+5


source






More articles:


All Articles