How to check from multiple Boolean columns at least one is true or all column values are false in SQL Server

Question

Table format:

+---------+---------+-----------------+
| column1 | column2 | column3| column4| 
+---------+---------+-----------------+
| value1  | true    | true   | false  |
| value2  | true    | false  | true   |
| value4  | false   | false  | false  |
+---------+---------+-----------------+

Column 1 to 4 has data type Boolean.

I want to check whether all these Boolean column values are false or at least one column value is true.

Is there any better way to do this using a SQL Server query? Expected output:

+---------+---------+-----------------+
| column1 | column2 | column3| column4| 
+---------+---------+-----------------+
| value1  | true    | true   | false  | = true
| value2  | true    | false  | true   | = true
| value4  | false   | false  | false  | = false
+---------+---------+-----------------+

Is column1 bit or not? value1, value2 and value4 means at least 3 possible non-NULL states for bit which can't happen — gbn, Mar 26 '18 at 06:58
Do you want to sum across _rows_ or across _columns_? This is the part where people leave comments that you should have shown your expected output. — Tim Biegeleisen, Mar 26 '18 at 07:02
@PriyankaDesai "across rows" means what to you? And what do you expect as output based on what you posted? Voting to close as unclear and [no attempt](http://idownvotedbecau.se/noattempt/) — SMor, Mar 26 '18 at 12:00
Sorry for not giving the clear output view. I got my solution from below answers. — Priyanka Desai, Mar 27 '18 at 06:56

score 8 · Answer 1 · answered Mar 26 '18 at 07:07

Just use bitwise operators if bit data type.

DECLARE @foo table (column1 bit, column2 bit, column3 bit, column4 bit)
INSERT @foo VALUES (1, 1, 0, 1), (0, 1, 0, 0), (0, 0, 0, 0)

SELECT
    CASE
        WHEN column1 | column2 | column3 | column4 = 0 THEN 'All false'
        ELSE 'Some true' 
    END
FROM @foo;

Very simple, no CASTs involved

Michał Turczyn · Answer 2 · 2018-03-26T07:06:33.270

1

In SQL Server, there's no boolean type, but there's a bit datatype, which I use instead, it's basically 0 and 1 (obviously). So your table would become table of zeros and ones, 0 - false, 1 - true. So to check if at least one column is true out of four, simply add all values and check if it's greater than zero (if all are false, then the sum wuold be equal to zero), like:

case when cast(column1 as int) + 
          cast(column2 as int) + 
          cast(column3 as int) + 
          cast(column4 as int) > 0 then 'at least one true' else 'all false' end

edited Mar 26 '18 at 07:06

answered Mar 26 '18 at 07:00

Michał Turczyn

32,028
14
47
69

You'll get an error, "The data types bit and bit are incompatible in the add operator." – gbn Mar 26 '18 at 07:02

Yogesh Sharma · Answer 3 · 2018-03-26T07:06:21.233

SQL Server doesn't have Boolean type, it has BIT type to represent boolean data

select a.column1, 
      case when sum(a.value) >= 1 
           then 'one or more is true' else 'all is false' end [CheckBoolVal]
from table t cross apply (
        values (column1, col2), (column1, col3), (column1, col4) )a(column1, value)
group by a.column1

Result :

column1 CheckBoolVal
value1  one or more is true
value2  one or more is true
value4  all is false

How to check from multiple Boolean columns at least one is true or all column values are false in SQL Server

3 Answers3