在sql中将逗号分隔的字符串转换为单独的行

Question

我有两个SQL变量@item1和@item2，它们的值分别为"abc,def","ghi,jkl"。

我有一个这样的表:

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

我想要的结果是:

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

我已经尝试在SQL Server中使用splitstring功能，但在这种情况下不工作。

What I have try

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

Answer 1

从SQL Server 2016开始，基于json的方法是一个可能的选择。其思想是将输入文本值转换为有效的JSON数组(abc,def转换为["abc","def"])，并用OPENJSON()解析该数组。结果是一个包含key、value和type列的表，key列保存解析数组中每个项的基于0的索引。

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]

注意，从SQL Server 2022开始，您可以使用SRING_SPLIT()和enable_ordinal参数来获得相同的结果:

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]