can i do this without using a loop?

Question

can i do this without using a loop?

manssourianm

Mr or Mrs. 500

Points: 556
More actions
April 6, 2012 at 11:58 am

#391968

hi, i have a table with two columns:
1)StopName
2)Distance_to_next_stop
i would like to end up with a table with 3 columns:
1)FromStop
2)ToStop
3)Distance
the records should be (assuming a table with 4 stops in it):
Distance from Stop A to Stop B
Distance from Stop A to Stop C
Distance from Stop A to Stop D
Distance from Stop B to Stop C
Distance from Stop B to Stop D
Distance from Stop C to Stop D
I hope i explained this adequately, thanks for any help given.
-mm

Viewing 12 posts - 1 through 11 (of 11 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I'm confident this can be done without a loop.

Please read the first article in my signature and post table def and sample data in a ready to use format so we have something to work with. In return you'll get a tested solution based on your sample data.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

Self join would do it meaning, you have to join your table with itself, apply your logic.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 3

Thanks for the advice mr. lutzm. my logic fails me on this one...

ok here's a table and sample data:

CREATE TABLE [dbo].[distancetest1](

[StopName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dist] [numeric](3, 1) NULL

) ON [PRIMARY]

INSERT INTO DISTANCETEST1 (StopName, dist)

select 'A', '3.5' UNION ALL

select 'B', '2.3' UNION ALL

Select 'C', '1.7' UNION ALL

Select 'D', '3.0'

The results would show 6 rows:

A to B, Distance,

A to C, Distance,

A to D, Distance,

B to C, Distance,

B to D, Distance,

C to D, Distance

thanks again, guys.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Whats the logic to find the distance betwen A and C ? is it ( A to B ) + (A to C) or is it value of distance in A + value of distance in C?

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Can you give the expected results based on your sample data? You have given a skelton of the desired result, but can u populate the expected results with the values from your sample data?

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 6

yes sir, here would be the result table:

CREATE TABLE [dbo].[distancetest2](

[FromStop] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ToStop] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[dist] [numeric](3, 1) NULL

) ON [PRIMARY]

INSERT INTO DISTANCETEST2 (FromStop, ToStop, dist)

select 'A','B', '3.5' UNION ALL

select 'A','C', '5.8' UNION ALL

Select 'A','D', '7.5' UNION ALL

Select 'B','C', '2.3' UNION ALL

Select 'B','D', '4.0' UNION ALL

Select 'C','D', '1.7'

thanks again for any help

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 7

ColdCoffee (4/6/2012)
Whats the logic to find the distance betwen A and C ? is it ( A to B ) + (A to C) or is it value of distance in A + value of distance in C?

the answer to this would be (A to B) + (B to C)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

How about this?

; WITH CTE AS

(

SELECT RN = ROW_NUMBER() OVER(ORDER BY DST.StopName) , DST.StopName, DST.dist

FROM distancetest1 DST

)

,ListOfStops AS

(

SELECT FromStop = OutQ.StopName

, ToStop = CrsApp.StopName

FROM CTEOutQ

CROSS APPLY

(

SELECT InrQr.StopName

FROM CTE InrQr

WHERE InrQr.RN > OutQ.RN

) CrsApp

)

SELECT Los.FromStop

,LoS.ToStop

,SUM(Base.dist) AS dist

FROM ListOfStops LoS

LEFT JOIN distancetest1 Base

ON Base.StopName >= LoS.FromStop AND Base.StopName < LoS.ToStop

GROUP BY Los.FromStop

,LoS.ToStop

ORDER BY Los.FromStop

,LoS.ToStop

I guess on a million-plus table, this is going to suck 🙁 let me check of another solution and another idea altogether!

LutzM SSC Guru Points: 107049 More actions · Answer 9

This solution depends on the StopName being ascending as shown in your sample data.

If that's not the case, you'll need to use another column that define whether a stop belongs to a route or not.

SELECT

d1.StopName AS FromStop,

d2.StopName AS ToStop,

t.dist

FROM #distancetest1 d1

INNER JOIN #distancetest1 d2 ON d1.StopName < d2.StopName

CROSS APPLY

(

SELECT

SUM(dist) dist

FROM #distancetest1 d3

WHERE

d3.StopName >= d1.StopName

AND d3.StopName < d2.StopName

) t

ORDER BY d1.StopName

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 10

wow, that works, i just have to decipher it now!

you're a genius, if you were a guitar player you'd be jimi hendrix...

the datasets are usually going to be really small (less than 40 records), so this solution is OK.

However, i won't stop you from coming up with variations, more things i can learn.

thanks!!

manssourianm Mr or Mrs. 500 Points: 556 More actions · Answer 11

i wish i had a fraction of your intellect...

thanks for taking the time to answer my question, i learned a new thing today!

-mm