Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 One Select statement

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-26 : 11:04:20
DL writes "Data Sample (primary keys: VendorName, Address1, Address2)

VendorName Address1 Address2
A A c/o
B c/o
C B
D c/o
E c/o

Each records can have multiple rules apply to based on this logic

When VendorName = Address1 then 1
When Address1 = 'c/o' or Address2 = 'c/o' then 2
When VendorName is not null and Address1 is null then 3
etc...

Results

FiVendorName Address1 Address2 Rules
A A c/o 1,2
B c/o 2
C B
D c/o 2,3
E c/o 2

How can I generate the Rules column by using just one SELECT statement.

Many Thanks for your help."

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-04-26 : 12:45:19
How about this?


create table #vendor (
VendorName varchar(20)
, Address1 varchar(20)
, Address2 varchar(20)
)

insert #vendor values ('A', 'A', 'c/o')
insert #vendor values ('B', null, 'c/o')
insert #vendor values ('C', 'B', null)
insert #vendor values ('D', null, 'c/o')
insert #vendor values ('E', null, 'c/o')

select * ,
Rules =
Substring(Cast(
Case When VendorName = Address1 then ',1' else '' end +
Case When Address1 = 'c/o' or Address2 = 'c/o' then ',2' else '' end +
Case When VendorName is not null and Address1 is null then ',3' else '' end
as Varchar(31)), 2, 30)
from #vendor

drop table #vendor

-- here is the output
VendorName Address1 Address2 Rules
--------------- --------------- --------------- --------------------
A A c/o 1,2
B NULL c/o 2,3
C B NULL
D NULL c/o 2,3
E NULL c/o 2,3



Go to Top of Page
   

- Advertisement -