Turning related Comma Separated strings into individual rows with multiple columns with one to many

I was looking at the post Turning related Comma Separated strings into individual rows with multiple columns. This works great for me when the data Data is one to one Related Data. But I need more data like Data to Many RelatedDate. I use [] to group each RelatedData to each section of Data.

| SomeID         | OtherID     | Data      | RelatedData |
+----------------+-------------+-----------+-------------+
| abcdef-.....   | cdef123-... | 18,20,22  | [xxx,xxxx],[xxxx],[yyy]|
| abcdef-.....   | 4554a24-... | 17,19     | [a],[bb]     |
| 987654-.....   | 12324a2-... | 13,19,20  | [r],
展开收缩
,[t]  |

I want to return rows like the following:

| SomeID         | OtherID     | Data | RelatedData |
+----------------+-------------+------+-------------+
| abcdef-.....   | cdef123-... | 18   | xxx         |
| abcdef-.....   | cdef123-... | 20   | xxxx        |
| abcdef-.....   | cdef123-... | 20   |             |
| abcdef-.....   | cdef123-... | 22   | yyy         |
| abcdef-.....   | 4554a24-... | 17   | a           |
| abcdef-.....   | 4554a24-... | 19   | bb          |
| ...            | ...         | ...  | ...         |

I've tried to modify one of the answers from the post to fit my needs but I have no luck as it is pushing my data to a new row.

;WITH cte AS (
  SELECT SomeId, OtherId, TRIM(cs1.Value) AS DataValue, TRIM(REPLACE(REPLACE(cs2.Value, '[', ''), ']', '')) AS RelatedDataValue,
    DataItemNumber = ROW_NUMBER() OVER (PARTITION BY t.SomeId, t.OtherId, cs1.Value ORDER BY (SELECT NULL)),
    RelatedDataItemNumber = ROW_NUMBER() OVER (PARTITION BY t.SomeId, t.OtherId, cs2.Value ORDER BY (SELECT NULL))
  FROM t
  CROSS APPLY STRING_SPLIT(Data, ',') cs1
  CROSS APPLY STRING_SPLIT(REPLACE(REPLACE(RelatedData, '[', ''), ']', ''), ',') cs2
)
SELECT cte.SomeId, cte.OtherId, cte.DataValue, cte.RelatedDataValue
FROM cte
WHERE cte.DataItemNumber = cte.RelatedDataItemNumber
ORDER BY cte.SomeId, cte.OtherId, cte.DataItemNumber;

This may only take you one step further, as it appears you have "related data" that is a one to many relationship to data. However this may assist. One of the great problems with string_split is that it does NOT supply the index reference of each split and so to get around this you can emulate this by using row_number. Alternatively find a "split string" user defined function and that does output the index (these do exist). So without going into too much detail, and using split_string, you can align the parts to each other this way:

Note I replaced ,[ and [ in RelatedData so that only ] is needed as the delimiter.

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

--DML !! NB SomeID is now unique !!
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],
展开收缩
,[t]');

3 rows affected

update T
set RelatedData = replace(replace(RelatedData,',[',''),'[','')

3 rows affected

select * from 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

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

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
*
from pdata
left join prelated on pdata.SomeID = prelated.SomeID
             and pdata.rn = prelated.rn
order by 1,2

fiddle 1

Note I believe that 18 may need to be further divided using a final string_split - but this varies to what is shown in the question.

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

fiddle 2