How to Get distinct data and with segmentid

Question

question

BASKARDHANHINDLAKSHIMI-4916 asked Oct 22, '21 BASKARDHANHINDLAKSHIMI-4916 commented Oct 22, '21

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-general sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

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

Answer 1 · 2021-10-22T10:11:25.89Z

Viorel-1 answered Oct 22, '21 BASKARDHANHINDLAKSHIMI-4916 commented Oct 22, '21

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

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

BASKARDHANHINDLAKSHIMI-4916 · Oct 22, 2021 at 10:31 AM

Thanks for script and valuable answer

0 ·

question