ten tourcodes in one column, Sql Server

Language: Layout:

DECLARE @TourCodes VARCHAR(4000);
SET @TourCodes =  REPLACE(REPLACE(
                  ' AVG123   , JGH12,AVasfG123,JGsdfH12,AVsdgG123,JsdgGH12 , A34G123,J56gGH12, A34G1df23,JgfGH12,
                    AVG1234   , JGH126,AVasfG1238,JGsdfH122,AVsdgG1235,JsdgGH127 , A34G1239,J56gGH12a, A34G1df23c,JgfGH12e,
                    AVG1235   , JGH127,AVasfG1239,JGsdfH123,AVsdgG1236,JsdgGH128 , A34G1230,J56gGH12b, A34G1df23d,JgfGH12f',
                   char(10),''),char(13),''); -- this is just a slightly extended sample input string

with rcrs AS (
select rtrim(ltrim(LEFT(@TourCodes,charindex(',',@TourCodes)-1))) first,
       
       SUBSTRING(@TourCodes+',',charindex(',',@TourCodes)+1,4000) other, 0 flg
union all
select rtrim(ltrim(LEFT(other,charindex(',',other)-1))) first,
       SUBSTRING(other,charindex(',',other)+1,4000) other , flg+1 flg FROM rcrs WHERE charindex(',',other)>0 and flg<30
    
), cmbn AS (
  SELECT a.flg/20 ii,(a.flg/2)%10 ij ,a.first+','+b.first tc FROM rcrs a
  LEFT JOIN rcrs b ON b.flg=a.flg+1
  WHERE a.flg%2=0
)
SELECT ii,'ABC' name,'Amazon' org, 
  MAX(CASE ij WHEN 0 THEN tc END)+' '+
  COALESCE(MAX(CASE ij WHEN 1 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 2 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 3 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 4 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 5 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 6 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 7 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 8 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 9 THEN tc END)+' ','') tourCodes
FROM cmbn GROUP BY ii

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 0, absolute service time: 0,2 sec