How to Get distinct data and with segmentid

Question

Hi All,

Kindly help me i have the below data required distinct segment id and with segment order.

Sample code as below

Declare @segmentdetails varchar(500)
DECLARE @temp TABLE (Segmentid int,from_airport char(3),to_airport char(3))

insert into @temp values(1,'MAA','CDG')
insert into @temp values(2,'CDG','CCJ')
insert into @temp values(3,'CCJ','TRV')
insert into @temp values(4,'TRV','MAA')

select
@segmentdetails =COALESCE(@segmentdetails + '/', '')+from_airport+'/'+to_airport
from @temp
select @segmentdetails

Current output : MAA/CDG/CDG/CCJ/CCJ/TRV/TRV/MAA

Desired ouput : MAA/CDG/CCJ/TRV/MAA

Answer 1

Try this script:

select top(1) @segmentdetails = from_airport + '/' + to_airport from @TEMP order by SegmentId 
select @segmentdetails = @segmentdetails + '/' + to_airport from @TEMP order by SegmentId offset 1 row
select @segmentdetails

and this:

select @segmentdetails = concat( (select top(1) from_airport from @TEMP order by SegmentId), '/', string_agg(to_airport, '/') within group (order by SegmentId))
from @TEMP
select @segmentdetails