SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Deleted question:
-- https://stackoverflow.com/questions/78427571/string-agg-function-equivalent-in-sql-server-2016#comment138266842_78427571
  
CREATE TABLE Data (
    Id INT,
    Route VARCHAR(100),
    LegNumber INT
)

INSERT Data 
VALUES 
    (1, 'JFK-LAX', 1),
    (1, 'LAX-IAD', 2), -- Fixed typo
    (1, 'IAD-ORD', 3),
    (5, 'MIA-ATL', 1),
    (5, 'ATL-MIA', 2),
    (2, 'SEA-IAH', 1),
    (2, 'IAH-AUS', 2),
    (2, 'AUS-SEA', 3)
8 rows affected

-- For SQL Server 2022 (and later):
-- 1. Use STRING_SPLIT(,,1) to separate the route components
-- 2. Use STRING_AGG() to combine the non-repeating codes
SELECT
    A.Id,
    STRING_AGG(A.value, '-')
        WITHIN GROUP(ORDER BY A.LegNumber, A.ordinal)
        AS Routing
FROM (
    SELECT
        D.Id, D.LegNumber, S.ordinal, S.value,
        LAG(S.value) OVER(PARTITION BY D.Id ORDER BY D.LegNumber, S.ordinal) AS PriorValue
    FROM Data D
    CROSS APPLY STRING_SPLIT(D.Route, '-', 1) S
) A
WHERE A.value <> ISNULL(A.PriorValue, '')
GROUP BY A.Id
ORDER BY A.Id


Id
Routing


1
JFK-LAX-IAD-ORD

2
SEA-IAH-AUS-SEA

5
MIA-ATL-MIA


-- For earlier versions,
-- 1. Use CHARINDEX(), LEFT(), and STUFF() to separate the route components
-- 2. Use the FOR XML technique to combine the non-repeating codes
SELECT I.Id, R.Routing
FROM (
    SELECT DISTINCT D.Id
    FROM Data D
) I
CROSS APPLY (
    SELECT
        STUFF((
            SELECT '-' + A.value
            FROM (
                SELECT
                    D.LegNumber, S.ordinal, S.value,
                    LAG(S.value) OVER(PARTITION BY D.Id ORDER BY D.LegNumber, S.ordinal) AS PriorValue
                FROM Data D
                CROSS APPLY (SELECT CHARINDEX('-', D.Route) AS DashPos) DP
                CROSS APPLY (
                    VALUES
                        (1, LEFT(D.Route, DP.DashPos - 1)),
                        (2, STUFF(D.Route, 1, DP.DashPos, '')) -- Strip the left, leaving the right
                ) S(ordinal, value)
                WHERE D.Id = I.Id
            ) A
            WHERE A.value <> ISNULL(A.PriorValue, '')
            ORDER BY A.LegNumber, A.ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)') , 1, 1, '') AS Routing
) R
ORDER BY I.Id


Id
Routing


1
JFK-LAX-IAD-ORD

2
SEA-IAH-AUS-SEA

5
MIA-ATL-MIA


-- For earlier versions,
-- 1. Use OPENJSON() to separate the route components
-- 2. Use the FOR XML technique to combine the non-repeating codes
SELECT I.Id, R.Routing
FROM (
    SELECT DISTINCT D.Id
    FROM Data D
) I
CROSS APPLY (
    SELECT
        STUFF((
            SELECT '-' + A.value
            FROM (
                SELECT
                    D.LegNumber, O.ordinal, S.value,
                    LAG(S.value) OVER(PARTITION BY D.Id ORDER BY D.LegNumber, O.ordinal) AS PriorValue
                FROM Data D
                CROSS APPLY OPENJSON('["' +  REPLACE(D.Route, '-', '","') + '"]') S
                CROSS APPLY(SELECT CAST(S.[key] AS INT) AS ordinal) O
                WHERE D.Id = I.Id
            ) A
            WHERE A.value <> ISNULL(A.PriorValue, '')
            ORDER BY A.LegNumber, A.ordinal
            FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(max)') , 1, 1, '') AS Routing
) R
ORDER BY I.Id


Id
Routing


1
JFK-LAX-IAD-ORD

2
SEA-IAH-AUS-SEA

5
MIA-ATL-MIA