SQL Server 2022

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

--DDL
CREATE TABLE T (
    SomeID VARCHAR(255),
    OtherID VARCHAR(255),
    Data VARCHAR(255),
    RelatedData VARCHAR(255)
);

--DML
INSERT INTO T (SomeID, OtherID, Data, RelatedData) 
VALUES 
('abcdef-1....', 'cdef123-...', '18,20,22', '[xxx,xxxx],[xxxx],[yyy]'),
('abcdef-2....', '4554a24-...', '17,19', '[a],[bb]'),
('987654-3....', '12324a2-...', '13,19,20', '[r],[s],[t]');
3 rows affected

update T
set RelatedData = replace(replace(RelatedData,',[',''),'[','')
3 rows affected

select * from t


SomeID
OtherID
Data
RelatedData


abcdef-1....
cdef123-...
18,20,22
xxx,xxxx]xxxx]yyy]

abcdef-2....
4554a24-...
17,19
a]bb]

987654-3....
12324a2-...
13,19,20
r]s]t]


select t.someID, cs1.*, row_number() over(partition by t.SomeID order by (select 1)) rn
FROM t
  CROSS APPLY STRING_SPLIT(Data, ',') cs1


someID
value
rn


987654-3....
13
1

987654-3....
19
2

987654-3....
20
3

abcdef-1....
18
1

abcdef-1....
20
2

abcdef-1....
22
3

abcdef-2....
17
1

abcdef-2....
19
2


select t.someID, cs2.*, row_number() over(partition by t.SomeID order by (select 1)) rn
FROM t
  CROSS APPLY STRING_SPLIT(RelatedData, ']') cs2


someID
value
rn


987654-3....
r
1

987654-3....
s
2

987654-3....
t
3

987654-3....

4

abcdef-1....
xxx,xxxx
1

abcdef-1....
xxxx
2

abcdef-1....
yyy
3

abcdef-1....

4

abcdef-2....
a
1

abcdef-2....
bb
2

abcdef-2....

3


with pdata as (
select t.someID, cs1.*, row_number() over(partition by t.SomeID order by (select 1)) rn
FROM t
  CROSS APPLY STRING_SPLIT(Data, ',') cs1
)
, prelated as (
select t.someID, cs2.*, row_number() over(partition by t.SomeID order by (select 1)) rn
FROM t
  CROSS APPLY STRING_SPLIT(RelatedData, ']') cs2
  )
select
    pdata.SomeID
  , pdata.value as data
  , cs3.value   as related
from pdata
inner join prelated on pdata.SomeID = prelated.SomeID
                   and pdata.rn = prelated.rn
CROSS APPLY STRING_SPLIT(prelated.value, ',') cs3
order by 1,2


SomeID
data
related


987654-3....
13
r

987654-3....
19
s

987654-3....
20
t

abcdef-1....
18
xxx

abcdef-1....
18
xxxx

abcdef-1....
20
xxxx

abcdef-1....
22
yyy

abcdef-2....
17
a

abcdef-2....
19
bb

SomeID	OtherID	Data	RelatedData
abcdef-1....	cdef123-...	18,20,22	xxx,xxxx]xxxx]yyy]
abcdef-2....	4554a24-...	17,19	a]bb]
987654-3....	12324a2-...	13,19,20	r]s]t]