Convert comma separated string into individual rows in sql-CodePudding

I have two SQL variables @item1 and @item2, which have values "abc,def" , "ghi,jkl" respectively.

And I have a table like this:

--------------------
Id | item1 | item2 |
--------------------

The result I want is:

--------------------
Id | item1 | item2 |
--------------------
1  | abc   | ghi   |
--------------------
2  | def   | jkl   |
--------------------

I have tried to use the splitstring function in SQL Server, but thats not working in this scenario.

What I have tried

Insert into table (item1,item2)
(Select * from Splitstring (@item1,',')),
(Select * from Splitstring (@item2, ','))

CodePudding user response：

Starting from SQL Server 2016, a JSON-based approach is a possible option. The idea is to transform the input text values into a valid JSON array (abc,def into ["abc","def"]) and parse this array with OPENJSON(). The result is a table with columns key, value and type and the key column holds the 0-based index of each item from the parsed array.

DECLARE @item1 varchar(max) = 'abc,def'
DECLARE @item2 varchar(max) = 'ghi,jkl'

INSERT INTO [Table] (item1, item2)
SELECT j1.[value], j2.[value]
FROM OPENJSON(CONCAT('["', REPLACE(@item1, ',', '","'), '"]')) j1
FULL OUTER JOIN OPENJSON(CONCAT('["', REPLACE(@item2, ',', '","'), '"]')) j2 
   ON j1.[key] = j2.[key]

Note, that starting from SQL Server 2022, you may use SRING_SPLIT() with the enable_ordinal parameter to get the same results:

INSERT INTO [Table] (item1, item2)
SELECT s1.[value], s2.[value]
FROM STRING_SPLIT(@item1, ',', 1) s1
FULL OUTER JOIN STRING_SPLIT(@item1, ',', 1) s2 
   ON s1.[ordinal] = s2.[ordinal]