Merge results of string_split operations and update table

Question

I have a table with 3 columns: col1, col2, col3. I need to insert rows into this table and my inputs are 3 parameters, one int param and two varchar params that I need to split. So for example

@param1 = 1    
@param2 = '2,3'
@param3 = '4,5'

the rows to be produced are

1,2,4
1,3,5

I tried to get near the desired result with such code

WITH group1 AS (
  SELECT @param1 as col1, value as col2 from string_split(@param2,',')
),
group2 AS (
  SELECT @param1 as col1, value as col3 from string_split(@param3,',') 
)
SELECT group1.col1, group1.col2, group2.col3
  FROM group1
  JOIN group2 ON group1.col1 = group2.col1 

but it's not exactly what I want.

Answer 1

You need a splitter, that returns each substring and the position of the substring. Sometimes a JSON-based approach may help. You need to transform the comma-separated strings into valid JSON arrays (2,3 into [2,3]), parse the arrays with OPENJSON() and use the appropriate JOIN:

DECLARE @param1 int = 1    
DECLARE @param2 varchar(100) = '2,3,6,8'
DECLARE @param3 varchar(100) = '4,5,9,5'

-- INSERT INTO Table1 (Column1, Column2, Column3)
SELECT 
   @param1 AS Column1,
   j1.[value] AS Column2, 
   j2.[value] AS Column3
FROM OPENJSON (CONCAT('[', @param2, ']')) j1
FULL JOIN OPENJSON (CONCAT('[', @param3, ']')) j2 ON j1.[key] = j2.[key]

Result:

Column1 Column2 Column3
1       2       4
1       3       5
1       6       9
1       8       5

As an important note (although STRING_SPLIT() splits a string into rows and returns a table), as is mentioned in the documentation the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.