sql - 合并两个 STRING_SPLIT

Question

考虑以下问题:我有两个要拆分的字符串:

STR1 = 'b;a;c;d;e'

STR2 = '3;1;4;2;5'

我想根据它们的索引拆分和合并这两个字符串，结果是:

b -> 3
a -> 1
c -> 4
d -> 2
e -> 5

我尝试使用 STRING_SPLIT，但 order by 将它们全部排序。

SELECT A.VALUE, B.VALUE FROM (
    SELECT VALUE, ROW_NUMBER() OVER(ORDER BY VALUE) AS RW
    FROM STRING_SPLIT('b;a;c;d;e', ';')
) A
INNER JOIN (
    SELECT VALUE, ROW_NUMBER() OVER(ORDER BY VALUE) AS RW
    FROM STRING_SPLIT('3;1;4;2;5', ';')
) B
ON A.RW = B.RW

这会产生以下结果:

a   1
b   2
c   3
d   4
e   5

Answer 1

也许是这样的？

declare @STR1 varchar(64) = 'b;a;c;d;e'

declare @STR2 varchar(64)  = '3;1;4;2;5'

;with cte as(
select
    value
    ,RN = row_number() over (order by (select null))
from
    STRING_SPLIT(@STR1,';')),

cte2 as(
select
    value
    ,RN = row_number() over (order by (select null))
from
    STRING_SPLIT(@STR2,';'))

select
    c.value + d.value
from
    cte c
    inner join 
    cte2 d on c.RN = d.RN