-- This will hold our routing-string:
DECLARE	@RoutingCode		VarChar(300)
-- This is a variable I use to specify a trip:
DECLARE	@RecordKey		VarChar(20)

-- This is just an arbitrary UID for a trip:
   SET	@RecordKey		= '1'

-- First we select the OriginCity of the first segment...
SELECT	@RoutingCode		= a.OriginCity
  FROM	TripInfo		a
 WHERE	a.UID		= @RecordKey
   AND	a.SegmentNum		= 1

-- Now we append each DestCity of ALL of the segments (including the first)
SELECT	@RoutingCode		= @RoutingCode + '/' + a.DestCity
  FROM	TripInfo		a
 WHERE	a.UID		= @RecordKey
ORDER BY a.SegmentNum

-- Display the routing-string:
SELECT	@RoutingCode

create table route ( UID int,SegmentNum int, OriginCity varchar(30), DestCity varchar(30) )

insert into route
select 1, 1, 'Dallas' ,'Houston' union all
select 1, 2, 'Houston' ,'New York'  union all
select 1, 3, 'New York' ,'Dallas'  union all
select 2, 1, 'Las Angeles' ,'Seattle'  union all
select 2, 2, 'Seattle' ,'Las Angeles' 


select r.uid,segmentnum, origincity,destcity,d.max_seg, replicate(' ',1000) as trip_route
into #route
from route r
join
(
	select uid, max(segmentnum) as max_seg
	from route
	group by uid
) d on d.uid = r.uid
order by r.uid, r.segmentnum

declare @uid int,@max_seg int,@route varchar(1000)
select @uid = 0, @route = '', @max_seg=0

update #route set @route = trip_route = 
case
	when uid <> @uid then origincity
	when uid = @uid and @max_seg > segmentnum then @route + '/' + origincity + '/'
	when uid = @uid and @max_seg = segmentnum then @route + origincity + '/' + destcity
	else origincity
end,
@max_seg = max_seg,
@uid = uid

select uid,max(trip_route)
from #route
group by uid

drop table route, #route

quote:

---

Originally posted by kselvia

You can do it with a loop, table variable, dynamic SQL, or complex correlated subqueries but none are as efficient as ehorn's solution. I actually did it and tested them but they sucked comparatively.

---