Sql query making to get result as below

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 2005 Forums

Transact-SQL (2005)

Sql query making to get result as below

Author

Topic

pragspict
Starting Member

3 Posts

Posted - 2014-12-16 : 05:58:15

I have a table containing below data and i want output such as
the cities in either direction should come only once.
i.e
if pune mumb
mumb pune is there we should get result as pune mumb only. Please reply ASAP.

SOURCE DEST
---------- -------
patna delhi
pat delh
delh pat
arni arni
arni dmn
delhi mumbai
pune mumb
mumb pune
pune bang
bang pune
hyd arni
hyd dmn
hyd dmn

expected o/p is :-
SOURCE DEST
---------- ----------
hyd arni
hyd dmn
patna delhi
delhi mumbai
arni dmn
pune mumb
pune bang
delh pat

Can someone please reply..

mhorseman
Starting Member

44 Posts

Posted - 2014-12-17 : 04:42:28

This seems to work, though there's probably a better way of doing it:

--Set up test data
drop table #x
create table #x (src varchar(10),dst varchar(10))

insert into #x
select 'patna','delhi' union all
select 'pat','delh' union all
select 'delh','pat' union all
select 'arni','arni' union all
select 'arni','dmn' union all
select 'delhi','mumbai' union all
select 'pune','mumb' union all
select 'mumb','pune' union all
select 'pune','bang' union all
select 'bang','pune' union all
select 'hyd','arni' union all
select 'hyd','dmn' union all
select 'hyd','dmn'

--Code
select distinct
case when src < dst then src else dst end,
case when src < dst then dst else src end
from #x

Note that this also gets the combiantion of "arni","arni"

Mark

pragspict
Starting Member

3 Posts

Posted - 2014-12-17 : 04:50:31

Thankyou soooooo much Mark , I am getting the result but Can you please explain me the query ,
as you have used same conditions in case so not able to understand that.

mhorseman
Starting Member

44 Posts

Posted - 2014-12-17 : 04:56:01

This so that the first location alphabetically of the pair is always in position 1, and the second is always in position 2. Then Distinct will get rid of any duplicate pairs.

Mark

pragspict
Starting Member

3 Posts

Posted - 2014-12-17 : 04:57:52

Got it mark , I was confused by looking at query but then understood.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources