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: Sunday, September 21, 2014 1:14 AM
Points: 4, Visits: 24

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 @ 9:01 AM
Points: 1,968, Visits: 20,571
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
and remember....every day is a school day
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: Sunday, September 21, 2014 1:14 AM
Points: 4, Visits: 24
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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 3,044, Visits: 8,669
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