Help with Query - SQL Server Forums

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

SQL Server Development (2000)

Help with Query

Author

Topic

jubinjose
Starting Member

20 Posts

Posted - 2007-02-28 : 21:06:23

I have a sql server 2000 table with fields Name, AddressType, Address
All are varchar fields and name field can be repeated maximum two times(one for business address and other for residence Address)

Sample data is as below

Name, AddressType, Address
---- ----------- -------
Jubin, Business, Jubins Business address
Jubin, Residence, Jubins Residence Address
Bill, Business, Bills business Address
Tim, Residence, Tims Residence Address

Can someone help me write a query that outputs in the format
Name, Residence Address, Business Address. If no residence address or business addres exists then I need 'NO Address' returned for that column. Expected Results

Jubin, Jubins Residence Address, Jubins Business Address
Bill, No Address, Bills Business Address
Tim, Tims Residence Address, No Address

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-28 : 21:28:59

[code]
select [Name] = coalesce(r.Name, b.Name),
[Residence Address] = coalesce(r.Address, 'NO Address'),
[Business Address] = coalesce(b.Address, 'NO Address')
from address r full outer join address b
on r.Name = b.Name
and r.AddressType = 'Residence'
and b.AddressType = 'Business'
[/code]

jubinjose
Starting Member

20 Posts

Posted - 2007-03-01 : 00:03:41

Thanks. But that doesn't give expected results. Above query result is

jubin, NO Address, jubins residence address
jubin, jubins residence address, jubins business address
bill, NO Address, bills business address
tim, NO Address, tims residence address
bill, bills business address, NO Address
tim, tims residence address, NO Address
jubin, jubins business address, NO Address

whereas I am trying to get

jubin, jubins residence address, jubins business address
bill, NO Address, bills business address
tim, tims residence address, NO Address

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 00:20:17

[code]
select [Name] = coalesce(r.Name, b.Name),
[Residence Address] = coalesce(r.Address, 'NO Address'),
[Business Address] = coalesce(b.Address, 'NO Address')
from (
select Name, Address
from address
where AddressType = 'Residence'
) r
full outer join
(
select Name, Address
from address
where AddressType = 'Business'
) b
on r.Name = b.Name
[/code]

jubinjose
Starting Member

20 Posts

Posted - 2007-03-01 : 00:29:33

That works. Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources