也许最好的解决方案是更改设计，但是如果需要解析输入数据，可以尝试基于json的方法。这个想法是把价值观从 column1 以及 column2 列转换为有效的json数组( my_fav_drink, my_fav_colour, my_fav_avenger 进入 ["my_fav_drink", "my_fav_colour", "my_fav_avenger"] )并使用 OPENJSON() 和默认架构。结果来自 OPENJSON() 执行是一个包含列的表 key , value 以及 type 以及 key 列是输入数组中元素的索引。
表格：

CREATE TABLE Data (
   column1 varchar(100),
   column2 varchar(100)
)
INSERT INTO Data (column1, column2)
VALUES ('my_fav_drink,my_fav_colour,my_fav_avenger', 'coffee,blue,dr_strange')

声明：

SELECT CONVERT(int, j1.[key]) + 1 AS [Number], j1.[value] AS [Label], j2.[value] AS [Value]
FROM Data d
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(d.column1, 'json'), ',', '","'), '"]')) j1
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(d.column2, 'json'), ',', '","'), '"]')) j2
WHERE j1.[key] = j2.[key]

结果：

Number  Label           Value
1       my_fav_drink    coffee
2       my_fav_colour   blue
3       my_fav_avenger  dr_strange

这是一个有点长的评论。
你存储值的方法真的非常糟糕。例如，保证两列具有相同数量的值是很麻烦的。然后想象一下如果其中一个值有逗号会发生什么！
基本上有四种选择。
如果你知道这些列是什么，就把它们存储起来！如果有些行有 NULL 价值观。那很好。如果您想更进一步，可以在不同的表中存储不同的列集。
（1）的一个变体是使用稀疏列。这允许比通常在行上允许更多的列——假设大多数值都是 NULL .
键值存储。也就是说，您将有另一个表，每个“实体”有多行，每个键/值对有一行。这样的eav数据模型（实体属性值）可能非常强大，但它通常假定所有值都是相同的类型，并且排除了外键关系的定义。
使用xml或json对值进行编码。与在列中放置已用字段相结合，这提供了可扩展性和通常合理的性能。
请注意，在一个字符串中存储多个值的方法以及在另一个字符串中存储头的方法不在此列表中。