SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #multitrans (
    chain VARCHAR(100)
)

INSERT INTO #multitrans
VALUES ('a|b|c|d'), ('aa|bb'), ('aaa|bbb|ccc|ddd|eee|fff')
3 rows affected

-- Conditional aggregation MAX(CASE ...END) can be used to select each desired value.
--
-- For SQL Server 2019 or earlier, where duplicate items do not exist,
-- CHARINDEX() may be used to reliably identify the item position.
-- (Do *not* use ORDER BY (SELECT NULL).)
SELECT TOP 1000 *
FROM (
    SELECT
        mt.chain, c.maxi, c.i,
        CONCAT('(', 
            c.value, ',',
            LEAD(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i)),
            ')') AS Edge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            MAX(CASE WHEN c.i = c.maxi THEN value END) OVER(PARTITION BY mt.Chain),
            ')') AS LastEdge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            LAST_VALUE(value) OVER(
                 PARTITION BY mt.Chain
                 ORDER BY c.i
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Important!
                 ),
            ')') AS AlsoLastEdge
    FROM #multitrans mt  -- temp table holding the |-sep graphs
    CROSS APPLY (
        SELECT i, value, MAX(i) OVER () AS maxi
        FROM(
            SELECT
                ROW_NUMBER() OVER (
                    ORDER BY CHARINDEX('|' + value + '|', '|' + chain + '|')
                    ) AS i,
                value 
            FROM STRING_SPLIT(Chain,'|')
        ) b
    ) c
) x
where x.i < x.maxi  -- Omit last item that has no follower
-- where x.i < x.maxi - 1  -- Omit the last edge too?
order by chain, i


chain
maxi
i
Edge
LastEdge
AlsoLastEdge


a|b|c|d
4
1
(a,b)
(c,d)
(c,d)

a|b|c|d
4
2
(b,c)
(c,d)
(c,d)

a|b|c|d
4
3
(c,d)
(c,d)
(c,d)

aa|bb
2
1
(aa,bb)
(aa,bb)
(aa,bb)

aaa|bbb|ccc|ddd|eee|fff
6
1
(aaa,bbb)
(eee,fff)
(eee,fff)

aaa|bbb|ccc|ddd|eee|fff
6
2
(bbb,ccc)
(eee,fff)
(eee,fff)

aaa|bbb|ccc|ddd|eee|fff
6
3
(ccc,ddd)
(eee,fff)
(eee,fff)

aaa|bbb|ccc|ddd|eee|fff
6
4
(ddd,eee)
(eee,fff)
(eee,fff)

aaa|bbb|ccc|ddd|eee|fff
6
5
(eee,fff)
(eee,fff)
(eee,fff)


Warning: Null value is eliminated by an aggregate or other SET operation.


DELETE FROM #multitrans

INSERT INTO #multitrans
VALUES ('a|b|c|b|a')
4 rows affected

-- For SQL Server 2019 or earlier, where duplicate items may exist,
-- use the OPENJSON(array) technique to reliably get the item position.
SELECT TOP 1000 *
FROM (
    SELECT
        mt.chain, c.maxi, c.i,
        CONCAT('(', 
            c.value, ',',
            LEAD(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i)),
            ')') AS Edge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            MAX(CASE WHEN c.i = c.maxi THEN value END) OVER(PARTITION BY mt.Chain),
            ')') AS LastEdge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            LAST_VALUE(value) OVER(
                 PARTITION BY mt.Chain
                 ORDER BY c.i
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Important!
                 ),
            ')') AS AlsoLastEdge
    FROM #multitrans mt  -- temp table holding the |-sep graphs
    CROSS APPLY (
        SELECT
            j.[key] + 1 AS i,
            j.value,
            MAX(j.[key] + 1) OVER () AS maxi
        FROM OPENJSON(
            '["' +  REPLACE(REPLACE(mt.chain, '"', '\"'), '|', '","') + '"]'
        ) j
    ) c
) x
where x.i < x.maxi  -- Omit last item that has no follower
-- where x.i < x.maxi - 1  -- Omit the last edge too?
order by chain, i


chain
maxi
i
Edge
LastEdge
AlsoLastEdge


a|b|c|b|a
5
1
(a,b)
(b,a)
(b,a)

a|b|c|b|a
5
2
(b,c)
(b,a)
(b,a)

a|b|c|b|a
5
3
(c,b)
(b,a)
(b,a)

a|b|c|b|a
5
4
(b,a)
(b,a)
(b,a)


Warning: Null value is eliminated by an aggregate or other SET operation.


-- For SQL Server 2022 and later, use the STRING_SPLIT() ordinal option.
SELECT TOP 1000 *
FROM (
    SELECT
        mt.chain, c.maxi, c.i,
        CONCAT('(', 
            c.value, ',',
            LEAD(value) OVER(PARTITION BY mt.Chain ORDER BY (c.i)),
            ')') AS Edge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            MAX(CASE WHEN c.i = c.maxi THEN value END) OVER(PARTITION BY mt.Chain),
            ')') AS LastEdge,
        CONCAT('(', 
            MAX(CASE WHEN c.i = c.maxi - 1 THEN value END) OVER(PARTITION BY mt.Chain),
            ',',
            LAST_VALUE(value) OVER(
                 PARTITION BY mt.Chain
                 ORDER BY c.i
                 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- Important!
                 ),
            ')') AS AlsoLastEdge
    FROM #multitrans mt  -- temp table holding the |-sep graphs
    CROSS APPLY (
        SELECT ss.ordinal AS i, ss.value, MAX(ss.ordinal) OVER () maxi
        FROM STRING_SPLIT(mt.chain, '|', 1) ss
    ) c
) x
where x.i < x.maxi  -- Omit last item that has no follower
-- where x.i < x.maxi - 1  -- Omit the last edge too?
order by chain, i


chain
maxi
i
Edge
LastEdge
AlsoLastEdge


a|b|c|b|a
5
1
(a,b)
(b,a)
(b,a)

a|b|c|b|a
5
2
(b,c)
(b,a)
(b,a)

a|b|c|b|a
5
3
(c,b)
(b,a)
(b,a)

a|b|c|b|a
5
4
(b,a)
(b,a)
(b,a)


Warning: Null value is eliminated by an aggregate or other SET operation.

chain	maxi	i	Edge	LastEdge	AlsoLastEdge
a\|b\|c\|d	4	1	(a,b)	(c,d)	(c,d)
a\|b\|c\|d	4	2	(b,c)	(c,d)	(c,d)
a\|b\|c\|d	4	3	(c,d)	(c,d)	(c,d)
aa\|bb	2	1	(aa,bb)	(aa,bb)	(aa,bb)
aaa\|bbb\|ccc\|ddd\|eee\|fff	6	1	(aaa,bbb)	(eee,fff)	(eee,fff)
aaa\|bbb\|ccc\|ddd\|eee\|fff	6	2	(bbb,ccc)	(eee,fff)	(eee,fff)
aaa\|bbb\|ccc\|ddd\|eee\|fff	6	3	(ccc,ddd)	(eee,fff)	(eee,fff)
aaa\|bbb\|ccc\|ddd\|eee\|fff	6	4	(ddd,eee)	(eee,fff)	(eee,fff)
aaa\|bbb\|ccc\|ddd\|eee\|fff	6	5	(eee,fff)	(eee,fff)	(eee,fff)

chain	maxi	i	Edge	LastEdge	AlsoLastEdge
a\|b\|c\|b\|a	5	1	(a,b)	(b,a)	(b,a)
a\|b\|c\|b\|a	5	2	(b,c)	(b,a)	(b,a)
a\|b\|c\|b\|a	5	3	(c,b)	(b,a)	(b,a)
a\|b\|c\|b\|a	5	4	(b,a)	(b,a)	(b,a)