Display 1 When Any Column Is 1 And 0 Otherwise by Using SIGN, SUM Or (Bitwise OR)

You have three columns; if any of the columns is 1 you want to display 1 if they are all 0 you want to display 0. how can you do that?
there are basically three ways to do this
1 SUM
2 SIGN
3 | (Bitwise OR)


Here are the examples

--create table and insert rows
CREATE TABLE #test (test_A INT,test_B INT,test_C INT)

INSERT INTO #test VALUES(0,0,0)
INSERT INTO #test VALUES(1,0,0)
INSERT INTO #test VALUES(0,1,0)
INSERT INTO #test VALUES(0,0,1)
INSERT INTO #test VALUES(0,1,1)
INSERT INTO #test VALUES(1,1,1)



First we will display 0 if all columns are 0; if any of the columns is 1 then we will display a one

--Using SIGN
SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test

--Using (Bitwise OR)
SELECT (test_A | test_B | test_C),*
FROM #test

--Using Sum
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 0 ELSE 1 END, *
FROM #test
GROUP BY test_A , test_B , test_C


Now let's say we want to display 1 if all the values are 1.
we can do that by using &; this is the Bitwise AND operator
--Using (Bitwise AND)
SELECT (test_A & test_B & test_C),*
FROM #test

-- SUM can also be used
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 3 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C


Now let's reverse the process and display 0 if any of the columns are 1 and 1 if all the columns are 0.
We have to use CASE to accomplish this

--Using CASE with SIGN
SELECT CASE SIGN(test_A+test_B+test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test

--Using CASE with Bitwise OR
SELECT CASE (test_A | test_B | test_C)
WHEN 1 THEN 0 ELSE 1 END, *
FROM #test


--Using SUM with CASE
SELECT CASE SUM(test_A + test_B + test_C)
WHEN 0 THEN 1 ELSE 0 END, *
FROM #test
GROUP BY test_A , test_B , test_C


Here is some additional stuff, add this row and run the queries again
INSERT INTO #test VALUES(1,NULL,1)

What happens when you run this?

SELECT SIGN(test_A+test_B+test_C) ,*
FROM #test

A NULL is returned; If you have NULLS in your table then you might want to use COALESCE

SELECT SIGN(test_A+COALESCE(test_B,0)+test_C) ,*
FROM #test