Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I select lists like this Expand / Collapse
Author
Message
Posted Saturday, April 19, 2014 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:35 AM
Points: 4, Visits: 22

Create table T
(
id INT primary key identity(1,1),
A0 varchar(10),
A1 varchar(10),
A2 varchar(10),
b0 varchar(10),
b1 varchar(10),
b2 varchar(10),
C0 bit default 0,
C1 bit DEFAULT 0,
C2 bit default 0
)

insert into T
select '00','01','02','00','00','00',1,0,0 union
select '01','01','01','00','00','00',1,0,0 union
select '00','01','02','00','01','02',1,1,1 union
select '00','01','02','00','00','00',1,1,0 union
select '00','01','02','00','00','00',1,0,1 union
select '01','01','01','01','00','01',1,0,1 union
select '00','01','02','00','00','00',0,1,1 union
select '00','01','02','00','01','02',0,1,1

search rule is: when c0=1 and a0=b0 then print.
Where any C0 C1 C2 value is 1, the corresponding A = B, then print out


the last result is:
'00','01','02','00','00','00',1,0,0 --C0=1 A0=B0
'00','01','02','00','01','02',1,1,1 -- c1,c2=1 a1=b1 a2=b2
'01','01','01','01','00','01',1,0,1 -- c1,c3=1 a1=b1 a3=b3
'00','01','02','00','01','02',0,1,1 -- c2,c3=1 a2=b2,a3=b3


Post #1563221
Posted Saturday, April 19, 2014 3:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 AM
Points: 1,899, Visits: 18,918
redcn2004 (4/19/2014)


search rule is: when c0=1 and a0=b0 then print.

the last result is:
'00','01','02','00','00','00',1,0,0 --C0=1 A0=B0



not sure I completely understand....for the first search rule if I run


SELECT id, A0, A1, A2, b0, b1, b2, C0, C1, C2
FROM T
WHERE (C0 = 1) AND (A0 = b0)


then I return 5 rows NOT the single row you show


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1563224
Posted Saturday, April 19, 2014 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 1:35 AM
Points: 4, Visits: 22
when c0=1 and a0=b0 then print
when c0=1 and c1=1 and a0=b0 and a1=b1 then print
when c1=1 and a1=b1 then print
when c1=1 and c2=1 and a1=b1 and a2=b2 then print

....
when C Prefix colunm =1 and A Prefix = B Prefix then print

And my english is not very good, so
Post #1563225
Posted Saturday, April 19, 2014 4:27 AM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 1,582, Visits: 4,331
redcn2004 (4/19/2014)
when c0=1 and a0=b0 then print
when c0=1 and c1=1 and a0=b0 and a1=b1 then print
when c1=1 and a1=b1 then print
when c1=1 and c2=1 and a1=b1 and a2=b2 then print

....
when C Prefix colunm =1 and A Prefix = B Prefix then print

And my english is not very good, so


Look at these code samples using the CASE statement:
SELECT * FROM (
SELECT
TT.id ,TT.A0 ,TT.A1 ,TT.A2 ,TT.b0
,TT.b1 ,TT.b2 ,TT.C0 ,TT.C1 ,TT.C2
,CASE
WHEN TT.C0 = 1 AND (TT.A0 = TT.b0) THEN 1
WHEN TT.C1 = 1 AND (TT.A1 = TT.b1) THEN 1
WHEN TT.C2 = 1 AND (TT.A2 = TT.b2) THEN 1
ELSE 0
END AS SHOW_FLAG
FROM T TT
) AS X WHERE X.SHOW_FLAG = 1;
GO

SELECT * FROM (
SELECT
TT.id ,TT.A0 ,TT.A1 ,TT.A2 ,TT.b0
,TT.b1 ,TT.b2 ,TT.C0 ,TT.C1 ,TT.C2
,CASE
WHEN TT.C0 = 1 AND (TT.A0 <> TT.b0) THEN 0
WHEN TT.C1 = 1 AND (TT.A1 <> TT.b1) THEN 0
WHEN TT.C2 = 1 AND (TT.A2 <> TT.b2) THEN 0
ELSE 1
END AS SHOW_FLAG
FROM T TT
) AS X WHERE X.SHOW_FLAG = 1;
GO

Post #1563233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse