CODEWITHSUNDEEP
sqlsql-serverbitwise-operators
Prashant Tiwari
Prashant Tiwari

Reputation: 344

How to perform bitwise OR operation between two rows in same SQL table?

I have an SQL table with a structure similar to the following:

  Name     Value
(varchar)  (bit)
__________________
 Val1       1
 Val2       1
 Val3       0
 Val4       0
 Val1       0
 Val2       0
 Val3       0
 Val4       1

So basically, I have two instances of the same row but with a different bit value. I want to retrieve distinct rows from this table, with the bit value being OR'ed. So for the given example, the result would look like:

Name    Value

Val1      1    (1 | 0)
Val2      1    (1 | 0)
Val3      0    (0 | 0)
Val4      1    (0 | 1)

Can this be achieved? I am working in Microsoft SQL Server 2012. Any kind of help is appreciated. Please do let me know if any further clarification is required.

Upvotes: 3

Views: 1294

Answers (3)

Devart
Devart

Reputation: 121912

DECLARE @t TABLE (Name VARCHAR(10) NOT NULL, Value BIT NOT NULL)

INSERT INTO @t (Name, Value)
VALUES
    ('Val1', 1), ('Val2', 1), ('Val3', 0), ('Val4', 0),
    ('Val1', 0), ('Val2', 0), ('Val3', 0), ('Val4', 1)

SELECT Name, MAX(Value % 2)
FROM @t
GROUP BY Name

output - 

---------- -----------
Val1       1
Val2       1
Val3       0
Val4       1

Upvotes: 1

Kiran Hegde
Kiran Hegde

Reputation: 3681

You can try this, hoping that value is numeric field with values 0 and 1.

SELECT Name, MAX(Value)
FROM [table]
GROUP BY Name

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Observing that "bitwise OR" has the same function as the aggregate function MAX (assuming all inputs are either 0 or 1, if any input is 1, the result is 1, otherwise 0) and accounting for the fact that we can't directly aggregate bit, this seems to work:

declare @t table (Name varchar(17) not null,Value bit not null)
insert into @t(Name,Value) values
('Val1',1),
('Val2',1),
('Val3',0),
('Val4',0),
('Val1',0),
('Val2',0),
('Val3',0),
('Val4',1)

select Name,CONVERT(bit,MAX(CONVERT(int,Value))) as Value
from @t group by Name

Upvotes: 9

Related Questions