CODEWITHSUNDEEP
sqlsql-server
martijn
martijn

Reputation: 1469

bitwise comparison in bit columns

I have a database table with columns shaped as following:

| ID | name | A | B | C | D |
|  1 |  foo | 1 | 0 | 0 | 1 |
|  2 |  bar | 0 | 0 | 1 | 1 |
|  3 |  foo | 1 | 1 | 0 | 0 |
|  4 |  bar | 1 | 1 | 0 | 0 |

A, B, C and D are bit columns.

I need to get the name values of the rows of which there at least two and that both have at least one identical bit column set to true. the result set I want to get for the given example is as following: 

| name | 
|  foo |

I can do the following:

SELECT l.name
FROM dummy l
INNER JOIN dummy r ON l.name = r.name
WHERE (l.A = 1 AND r.A = 1) 
OR (l.B = 1 AND r.B = 1) 
OR (l.C = 1 AND r.C = 1)
OR (l.D = 1 AND r.D = 1)
GROUP BY l.name
HAVING COUNT(*) > 1

But this gets unreadable soon since the table is massive. I was wondering if there was a bitwise solution to solve this

Upvotes: 0

Views: 1019

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

In similar way you could also use Apply opertaor

SELECT a.name FROM table t
CROSS APPLY (
    VALUES (name, 'A', A), (name, 'B', B), (name, 'C', C), (name, 'D', D)
)a(name , names , value)
WHERE a.value = 1 
GROUP BY a.name, a.Names, a.value
HAVING COUNT(*) > 1

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

It is not hard to read. It is just long.
This would be more efficient: 

SELECT distinct l.name
FROM       dummy l
INNER JOIN dummy r 
   ON l.name = r.name
  and l.id < r.id 
  and (    (l.A = 1 AND r.A = 1) 
        OR (l.B = 1 AND r.B = 1) 
        OR (l.C = 1 AND r.C = 1)
        OR (l.D = 1 AND r.D = 1) 
      )
order by l.name

You could build it up reading sys.columns

I don't think TSQL has any bitwise operators.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I suspect that your data model is wrong. It feels like A-D represent the same "type" of thing and so the data ought to be represented using a single column that contains the data values A-D and (if necessary) one column to store the 1 or 0, with separate rows for each A-D value. (But then, of course, we can use the presence of a row to indicate a 1 and the absence of the row to represent a 0).

We can use UNPIVOT to get this "better" structure for the data and then the query becomes trivial:

declare @t table (ID int not null, name char(3) not null, A bit not null, B bit not null,
                                                          C bit not null, D bit not null)
insert into @t(ID,name,A,B,C,D) values
(1,'foo',1,0,0,1),
(2,'bar',0,0,1,1),
(3,'foo',1,1,0,0),
(4,'bar',1,1,0,0)

;With ProperLayout as (
    select ID,Name,Property,Value
    from @t t
        unpivot (Value for Property in (A,B,C,D)) u
    where Value = 1
)
select name,Property
from ProperLayout
group by name,Property
having COUNT(*) > 1

Result:

name Property
---- ---------
foo  A

(Note also that the top of my script is not much different in size to the sample data in your question but has the massive benefit that it's runnable)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

From your description, you seem to want:

SELECT l.name
FROM dummy l
GROUP BY l.name
HAVING SUM( CAST(A as int) ) >= 2 OR
       SUM( CAST(B as int) ) >= 2 OR
       SUM( CAST(C as int) ) >= 2 OR
       SUM( CAST(D as int) ) >= 2 ;

This is based on the description. I don't know what the same result row has to do with the question.

Upvotes: 0

Related Questions