Travel Planner SQL

Question

Post reply

Travel Planner SQL

cbalasuriya

SSC Journeyman

Points: 86
More actions
October 6, 2010 at 9:32 am

#387950

Hi All
Can someone please tell me if value1 and 2 are on same table how to get a result sets similar to
F ---> A and A ---> B Using TSQL (2005)
ROW-----VALUE 1----VALUE 2
1---------A---------- B
2---------C---------- D
3---------E-----------F
4---------G-----------H
results
F ---> A
--------
F
E
D
C
B
Thanks in advance
A
OR
A ----> D
---------
A
B
C
D

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

not sure if this is what you are after;

Peso is one of the forum members with a strong grasp on this;

there is a thread about Dijkstra's Shortest Path Algorithm, which is the kind of thing you use to calculate the shortest routes between two points, but the implementations i've seen require a distance between 'a' and 'd', for example.

is that what you are after?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

cbalasuriya (10/6/2010)
Hi All
Can someone please tell me if value1 and 2 are on same table how to get a result sets similar to
F ---> A and A ---> B Using TSQL (2005)
ROW-----VALUE 1----VALUE 2
1---------A---------- B
2---------C---------- D
3---------E-----------F
4---------G-----------H
results
F ---> A
--------
F
E
D
C
B
Thanks in advance
A
OR
A ----> D
---------
A
B
C
D

hi

I have used pivot method

declare @table table (rowid int,value1 varchar(2),value2 varchar(2))

declare @table1 table (value varchar(220))

insert into @table

Select 1,'A','B'

union Select 2,'C','D'

union Select 3,'E','F'

union Select 4,'G','H'

declare @i int

set @i=3 /*From A-F*/

SELECT rowid, value12, value

--SELECT value

FROM

(SELECT rowid, value1, value2

FROM @table where rowid<=@i) p

UNPIVOT

(value FOR value12 IN

(value1, value2)

)AS unpvt;

GO

Remove the commented select line and see you will get some idea.

Thanks

Parthi

Thanks
Parthi

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 3

parthi-1705 (10/6/2010)
cbalasuriya (10/6/2010)
Hi All
Can someone please tell me if value1 and 2 are on same table how to get a result sets similar to
F ---> A and A ---> B Using TSQL (2005)
ROW-----VALUE 1----VALUE 2
1---------A---------- B
2---------C---------- D
3---------E-----------F
4---------G-----------H
results
F ---> A
--------
F
E
D
C
B
Thanks in advance
A
OR
A ----> D
---------
A
B
C
D
hi
I have used pivot method
declare @table table (rowid int,value1 varchar(2),value2 varchar(2))
declare @table1 table (value varchar(220))
insert into @table
Select 1,'A','B'
union Select 2,'C','D'
union Select 3,'E','F'
union Select 4,'G','H'
declare @i int
set @i=3 /*From A-F*/
SELECT rowid, value12, value
--SELECT value
FROM
(SELECT rowid, value1, value2
FROM @table where rowid<=@i) p
UNPIVOT
(value FOR value12 IN
(value1, value2)
)AS unpvt;
GO
Remove the commented select line and see you will get some idea.
Thanks
Parthi

Ummmm.... since non of the two point paths have anything in common, how do you propose resolving the F-->A path?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Victor Dvoryaninov SSC Veteran Points: 208 More actions · Answer 4

declare @table table (rowid int,value1 varchar(2),value2 varchar(2))

declare @table1 table (value varchar(220))

declare @first_Letter varchar(2)= 'A'

declare @last_Letter varchar(2)= 'F'

insert into @table

Select 1,'A','B'

union Select 2,'C','D'

union Select 3,'E','F'

union Select 4,'G','H'

;with t2 as(

select

new_rowid=2*(rowid - 1) + i,

rowid,

value=case i when 1 then value1 else value2 end

from @table

cross join (select i=1 union all select 2)i

)

select value from t2

where

( Value between @first_Letter and @last_Letter )

OR

( Value between @last_Letter and @first_Letter )

order by new_rowid * ( case when @first_Letter <= @last_Letter then 1 else -1 end )

cbalasuriya SSC Journeyman Points: 86 More actions · Answer 5

what if these are railway stations and

results

F ---> A

--------

train1

-----

F

E

train2

------

E

D

C

B

How to query then

cbalasuriya SSC Journeyman Points: 86 More actions · Answer 6

Hi

What if this is a layout of a Railway network (Letters are stations with juctions)

LINE are Tracks

Train1 - goes A - C

Train2 - goes C - F

Train3 - goes G - K

Train4 - goes K - M

PLEASE IGNORE " # "

M -N - O -P

## ¦

K - L - H - I - J

#####¦

G - D - E - F

## ¦

A - B - C

RESULTS

------------

Train----StationDepart----StationArrive

1----------A

1---------------------------B

2----------B

2---------------------------L

3----------L

3---------------------------M

cbalasuriya SSC Journeyman Points: 86 More actions · Answer 7

cbalasuriya

SSC Journeyman

Points: 86

November 9, 2010 at 5:18 am

#1248164

Any one?? please

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 8

My apologies... I just don't understand the problem. Please see my previous comment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)