Sql 删除与破折号()链接的相互重复的字符

除了开始和结束之外，它们都是重复的.所以首先不要重复代码！(假设每个机场代码为三个字符.)

SELECT ID, (
    SELECT case when rn = 1 then ROUTE else '-' + right(ROUTE, 3) end
    FROM DST d2
    WHERE ID = d.ID
    ORDER BY LNO
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS Route
FROM DST d
GROUP BY ID;

但考虑到字符串的长版本，并且知道所有代码的长度相同，并且如果您对机场数量有上限，那么您当然可以接受这样的长嵌套表达的 idea :

with dataX as (
    SELECT ID, STUFF((
        SELECT '-' + ROUTE
        FROM DST
        WHERE ID = d.ID
        ORDER BY LNO
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Route
    FROM DST d
    GROUP BY ID
)
select Route7
from dataX cross apply (values(coalesce(stuff(Route,   9, 4, ''), Route ))) v2(Route2)
           cross apply (values(coalesce(stuff(Route2, 13, 4, ''), Route2))) v3(Route3)
           cross apply (values(coalesce(stuff(Route3, 17, 4, ''), Route3))) v4(Route4)
           cross apply (values(coalesce(stuff(Route4, 21, 4, ''), Route4))) v5(Route5)
           cross apply (values(coalesce(stuff(Route5, 25, 4, ''), Route5))) v6(Route6)
           cross apply (values(coalesce(stuff(Route6, 31, 4, ''), Route6))) v7(Route7);

或

...
from dataX cross apply (values(stuff(Route + replicate(' ', 100), 9, 4, ''))) v2(Route2)
           cross apply (values(stuff(Route2, 13, 4, ''))) v3(Route3)
           cross apply (values(stuff(Route3, 17, 4, ''))) v4(Route4)
           cross apply (values(stuff(Route4, 21, 4, ''))) v5(Route5)
           cross apply (values(stuff(Route5, 25, 4, ''))) v6(Route6)
           cross apply (values(rtrim(stuff(Route6, 31, 4, '')))) v7(Route7)

https://dbfiddle.uk/wArraPC0