CREATE TABLE #Test
  (ID int NOT NULL IDENTITY(1,1) Primary key,
	TranID int NOT NULL,
	OriginCode varchar(5) NOT NULL,
	DestinationCode varchar(5) NOT NULL
  )
  GO
  
  INSERT INTO #Test
  SELECT 201205 ,'AMD','DOH'
  UNION ALL 
  SELECT 201205 ,'DOH','NBO'
  UNION ALL 
  SELECT 201205 ,'NBO','ADD'
  UNION ALL 
  SELECT 201205 ,'ADD','DEL'
  UNION ALL 
  SELECT 201205 ,'DEL','AMD'
  UNION ALL
  SELECT 201206 ,'IXJ','RMI'
  GO
  
  SELECT * FROM #Test
  

  DROP TABLE #Test

TranID	Code
201205	AMDDOHNBOADDDELAMD
201206	IXJRMI

select TranID , OriginCode + DestinationCode 'Code' from #Test

set nocount on 
 CREATE TABLE #Test
  (ID int NOT NULL IDENTITY(1,1) Primary key,
	TranID int NOT NULL,
	OriginCode varchar(5) NOT NULL,
	DestinationCode varchar(5) NOT NULL
  )
  GO
 CREATE TABLE #Test2
  (	TranID int NOT NULL,
	Code varchar(100) NOT NULL,
  )
go
  
  INSERT INTO #Test
  SELECT 201205 ,'AMD','DOH'
  UNION ALL 
  SELECT 201205 ,'DOH','NBO'
  UNION ALL 
  SELECT 201205 ,'NBO','ADD'
  UNION ALL 
  SELECT 201205 ,'ADD','DEL'
  UNION ALL 
  SELECT 201205 ,'DEL','AMD'
  UNION ALL
  SELECT 201206 ,'IXJ','RMI'
  GO

DECLARE @Tran_id int, @Tran_name nvarchar(50)

  
DECLARE test_cursor CURSOR FOR 
SELECT TranID, OriginCode + DestinationCode FROM #Test

OPEN test_cursor

FETCH NEXT FROM test_cursor 
INTO @Tran_id, @Tran_name

  WHILE @@FETCH_STATUS = 0
BEGIN
if exists ( select 'x' from #Test2 where TranID = @Tran_id)
Begin
update #Test2
set code = code+  @Tran_name
where TranID = @Tran_id
end

else 
begin
insert into #Test2 select @Tran_id, @Tran_name
end
FETCH NEXT FROM test_cursor 
INTO @Tran_id, @Tran_name

End

CLOSE test_cursor
DEALLOCATE test_cursor
 

select * from #Test2

  DROP TABLE #Test
DROP TABLE #Test2

set nocount off

SELECT 
tranid, 
( 
    SELECT OriginCode + DestinationCode
    FROM #test p2 
    WHERE p1.tranid = p2.tranid 
    FOR XML PATH('')
)
FROM #test p1 
GROUP BY tranid

WITH CTE AS (
SELECT ID, TranID, 
CASE WHEN a.N = 1 THEN OriginCode ELSE DestinationCode END AS code,
ROW_NUMBER() OVER(PARTITION BY TranID ORDER BY ID, a.N) AS pos
FROM #Test
CROSS JOIN (SELECT 1 UNION ALL SELECT 2)a(N)),
CTE2 AS (
SELECT ID, TranID, code, CAST(code AS VARCHAR(MAX)) AS overallCode, pos
FROM CTE
WHERE pos = 1
UNION ALL 
SELECT b.ID, b.TranID, b.code, overallCode + b.code, b.pos
FROM CTE2 a
INNER JOIN CTE b ON a.TranID = b.TranID AND a.pos < b.pos AND a.code <> b.code)
SELECT TranID, overallCode
FROM (SELECT TranID, overallCode, ROW_NUMBER() OVER(PARTITION BY TranID ORDER BY LEN(overallCode) DESC)
    FROM CTE2) a(TranID, overallCode, ItemNumber)
WHERE ItemNumber = 1;

TranID      overallCode
----------- --------------------
201205      AMDDOHNBOADDDELAMD
201206      IXJRMI