Having comma delimited lists in the database is an anti pattern and having multiple such comma delimited lists that need to be correlated based on order in the list is a huge anti pattern.
This is something that should be represented in a different table.
Hopefully the purpose of this query is to do so. A couple of alternatives....
As the ordinal to
STRING_SPLIT is apparently not available to you - you can use
OPENJSON as an alternative.
The below will work for SQL Server 2016+ (and you must be on at least that as you do have
STRING_SPLIT sans ordinal).
You may need to add code to escape characters in
c if you find that they contain characters that lead to invalid JSON arrays being constructed.
FROM test t
CROSS APPLY (SELECT b = MAX(CASE WHEN col = 'b' THEN value END),
c = MAX(CASE WHEN col = 'c' THEN value END),
[key] = cast([key] as int)
FROM (SELECT value,
'b' AS col
FROM OPENJSON(N'["' + REPLACE(t.b, ',', N'","') + N'"]') AS x
'c' AS col
FROM OPENJSON(N'["' + REPLACE(t.c, ',', N'","') + N'"]') AS x) vals
GROUP BY [key]) ca
ORDER BY id,[key]
If you were to have many such columns to deal with you could even consider a recursive CTE approach. This does add some overhead vs other approaches but it does allow you to do multiple columns per iteration and avoids any need to group or join them by ordinal afterwards.
WITH R AS
bpos0 = 0,
bpos = CHARINDEX(',', b),
cpos0 = 0,
cpos = CHARINDEX(',', c),
lvl = 1
FROM test t
bpos0 = R.bpos,
bpos = CASE WHEN R.bpos > 0 THEN CHARINDEX(',', b, R.bpos + 1) END,
cpos0 = R.cpos,
cpos = CASE WHEN R.cpos > 0 THEN CHARINDEX(',', c, R.cpos + 1) END,
lvl = lvl+1
WHERE R.bpos > 0 OR R.cpos > 0
b = SUBSTRING(b, bpos0 + 1, case when bpos = 0 then 80000 else bpos - bpos0 -1 end),
c = SUBSTRING(c, cpos0 + 1, case when cpos = 0 then 80000 else cpos - cpos0 -1 end)
ORDER BY id, lvl