question

BASKARDHANHINDLAKSHIMI-4916 avatar image
0 Votes"
BASKARDHANHINDLAKSHIMI-4916 asked BASKARDHANHINDLAKSHIMI-4916 commented

How to Get distinct data and with segmentid

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


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered BASKARDHANHINDLAKSHIMI-4916 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for script and valuable answer

0 Votes 0 ·