if object_id('dbo.tmpTable') is not null

drop table dbo.tmpTable

go

create table dbo.tmpTable(Slno int, ParentId int, Leg int, Orign char(1), Dest char(1))

insert into dbo.tmpTable

select 1, 1, 1, 'V', 'X'

union all select 2, 1, 2, 'X','Y'

union all select 3, 1, 3, 'Y','Z'

go

--when you can use Leg as the order

declare @concat nvarchar(max)

select @concat=coalesce(@concat,Orign)+'-'+Dest

from dbo.tmpTable where Parentid=1 order by Leg

select @concat

--or order is not known:

if object_id('dbo.fn_GetTripDetails') is not null

drop function dbo.fn_GetTripDetails

go

create function dbo.fn_GetTripDetails(@ParentID int)

returns nvarchar(max)

as

begin

declare @concat nvarchar(max)

;with Tab ([Concat],[Last],lvl)

as

(select convert(nvarchar(max),Orign+'-'+Dest),Dest,0 as lvl

from dbo.tmpTable t1 where ParentId=@ParentID and not exists

(select * from dbo.tmpTable t2 where t1.Orign=t2.Dest and t1.ParentiD=t2.ParentID)

union all

select [Concat]+'-'+t2.Dest,t2.dest,t.lvl+1

from Tab t

inner join dbo.TmpTable t2

on t.Last=t2.Orign

and t2.ParentiD=@ParentID

)

select top 1 @concat=[Concat] from Tab order by lvl desc;

RETURN @concat

end

go

select dbo.fn_GetTripDetails(1)