Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
I have sql table like below....

SQL
Id	Bag	Tapee	Band	shoes	knife	guitar	watch	umbrella hammer
1	1	0	1	1	0	1	1	1	  0
2	0	0	1	1	0	1	1	0	  0
3	0	0	1	1	0	0	0	0	  0

i wanna do AND Operation using transcation id....
For example i do AND Operation for transcation id 1 and 2 means
we can get 0 0 1 1 0 1 1 0 0.....
How to do like this AND Operation for all Transcation id like transcation id 1&2, 1&3,1&2&3
How to we perform this operation in sql or c#.net
Posted
Updated 18-Feb-13 23:56pm
v2
Comments
Simon_Whale 19-Feb-13 5:50am    
you need to expand your question as it is not very clear on what you are after doing

SQL
create table #bitoprt(id int identity(1,1),bag bit,tapee bit,band bit,knife bit,guitar bit,watch bit,umbrella bit,hammer bit)
insert into #bitoprt
values
(0, 1,  1,  0,  1,  1,  1,    0),
    (   0,  1,  1,  0,  1,  1,  0,    0),
    (   0,  1,  1,  0,  0,  0,  0,    0)


select (bt.bag & bt1.bag)bag,(bt.band & bt1.band)band,(bt.guitar & bt1.guitar)guitar
,(bt.hammer&bt1.hammer)hammer,(bt.knife&bt1.knife)knife
,(bt.tapee &bt1.tapee)tapee,(bt.umbrella&bt1.umbrella)umbrella,(bt.watch&bt1.watch)watch
from #bitoprt bt
join #bitoprt bt1 on bt.id = bt1.id+1
where bt.id in(1,2)
 
Share this answer
 
Comments
Deenuji 19-Feb-13 7:08am    
i didnt get any values on this
Arun1_m1 19-Feb-13 7:20am    
For which scenario you were not able to get any values. Can you also post the code that you have tried
Deenuji 19-Feb-13 9:48am    
ur given coding....
RedDk 19-Feb-13 16:14pm    
bag band guitar hammer knife tapee umbrella watch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0 1 1 0 0 1 0 1

Looks right to me.
RedDk 19-Feb-13 16:15pm    
Whatever Arun,

Gnarley!
Hi,

Try below sql

SQL
select min(bag),min(Tapee),min(Band),min(shoes),min(knife),min(guitar),min(watch),min(umbrella),min(hammer)
 from [dbo].[transcation] where id in(1,2)


Answer is
SQL
0	0	1	1	0	1	1	0	0
 
Share this answer
 
v2
Comments
RedDk 25-Feb-13 16:46pm    
Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for min operator.
Shanalal Kasim 26-Feb-13 7:19am    
In order to use the MIN function on a bit column, you need to convert to something that the MIN function does work with. In this case we can convert to int

select Min(convert(int,bit_column_name)) from TableName
here is how to do it in SQL Server

& (Bitwise AND) (Transact-SQL)[^]
 
Share this answer
 
Comments
Deenuji 19-Feb-13 7:11am    
i didnt get my answer....sorry
use bellow SQL Syntax

SQL
select * from ranscation where ranscationid between 1 and 2
 
Share this answer
 
Comments
Deenuji 19-Feb-13 6:09am    
thanks for ur solution but it 's wrong because it shows
Id Bag Tapee Band shoes knife guitar watch umbrella hammer
1 1 0 1 1 0 1 1 1 0
2 0 0 1 1 0 1 1 0 0
Because we using between operatore means we can't result AND Operation....so pls suggest any other ideas
try to read this one.
i hope you can get an idea of this.

http://msdn.microsoft.com/en-us/library/aa276873(v=sql.80).aspx[^]
 
Share this answer
 
Comments
Deenuji 19-Feb-13 7:11am    
i didnt get my answer....sorry

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900