SQL如何使用STRING_SPLIT将~分隔字符串按奇偶行拆分至两列

原有代码问题 #

• 你使用的ROW_NUMBER() OVER (ORDER BY C.CID)是按表主键CID排序，和字符串拆分后的元素顺序没有关联，无法正确标记拆分后元素的奇偶位
• 原有CASE语句将奇偶位值输出到同一列，无法得到两列并排的效果
• 低版本SQL Server的STRING_SPLIT默认不返回拆分元素的位置序号，不能直接用来判断顺序

实现方案 #

方案1：SQL Server 2022+/Azure SQL 版本（支持STRING_SPLIT序号参数） #

直接开启STRING_SPLIT的enable_ordinal参数获取拆分元素的顺序序号，按每两个元素分组聚合得到两列结果：

SELECT 
    c.CID,
    c.nunique,
    MAX(CASE WHEN s.ordinal % 2 = 1 THEN s.value END) AS 奇位列,
    MAX(CASE WHEN s.ordinal % 2 = 0 THEN s.value END) AS 偶位列
FROM Candidate c
CROSS APPLY STRING_SPLIT(c.applist, '~', 1) s 
GROUP BY c.CID, c.nunique, (s.ordinal + 1) / 2
ORDER BY c.CID, (s.ordinal + 1) / 2

方案2：低版本SQL Server兼容方案 #

用JSON拆分的方式保证拆分顺序，JSON返回的key字段天然对应拆分元素的位置序号：

SELECT 
    c.CID,
    c.nunique,
    MAX(CASE WHEN j.[key] % 2 = 0 THEN j.value END) AS 奇位列,
    MAX(CASE WHEN j.[key] % 2 = 1 THEN j.value END) AS 偶位列
FROM Candidate c
CROSS APPLY OPENJSON(CONCAT('["', REPLACE(c.applist, '~', '","'), '"]')) j
GROUP BY c.CID, c.nunique, (j.[key] + 1) / 2
ORDER BY c.CID, (j.[key] + 1) / 2

效果验证 #

以你给出的示例字符串a~b~c~d~e~f为例，执行后输出结果为：

完全符合需求。

内容的提问来源于stack exchange，提问作者user17250419