Query Help

Question

Query Help

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
December 5, 2014 at 12:11 am

#295657

Hi ,
I know this could be simple but i am not getting it. Could anybody please help me with his.
Consider the below code snippet
CREATE TABLE #TEMP(City1 varchar(20),City2 varchar(20),Distance int)
INSERT INTO #TEMP VALUES('X','Y',60)
INSERT INTO #TEMP VALUES('Y','X',60)
INSERT INTO #TEMP VALUES('A','B',70)
INSERT INTO #TEMP VALUES('B','A',70)
INSERT INTO #TEMP VALUES('C','D',80)
INSERT INTO #TEMP VALUES('D','C',80)
I am trying to get the result as
City1 City2 Distance
X Y 60
A B 70
C D 80

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

There are many ways to do that. For example

select c1 = case when City2 > City1 then City1 else City2 end

, c2 = case when City2 > City1 then City2 else City1 end

-- may be min() ?

, d = max(Distance)

from #TEMP

group by case when City2 > City1 then City1 else City2 end

,case when City2 > City1 then City2 else City1 end

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 3

Koen Verbeeck (12/5/2014)
What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?

Well, there is no business logic. It can be anything, i was trying out this for fun, since last 1 day 🙂

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 4

serg-52 (12/5/2014)
There are many ways to do that. For example
select c1 = case when City2 > City1 then City1 else City2 end
, c2 = case when City2 > City1 then City2 else City1 end
-- may be min() ?
, d = max(Distance)
from #TEMP
group by case when City2 > City1 then City1 else City2 end
,case when City2 > City1 then City2 else City1 end

As you said there are many ways, could you please hint out that what are other ways by which we can do this e.g.we can do this using XXXX (e.g. Pivot table)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

Shadab Shah (12/5/2014)
Koen Verbeeck (12/5/2014)
What is your business logic? Why would you select (X,Y,60) and not (Y,X,60)?
Well, there is no business logic. It can be anything, i was trying out this for fun, since last 1 day 🙂

🙂

select * from #TEMP where City1< City2

select min(city1), max(city2), distance

from #TEMP

group by distance