SQL Server中如何将Question、Reply两列拆分为Q1、R1、Q2、R2四列

两列同时拆分的核心是保证Question和Reply拆分后的元素位置一一对应，按位置取对应元素拼接为新列即可，以下为不同数据库的实现示例（默认拆分分隔符为|，可根据实际场景替换）：

MySQL 8.0及以上版本 #

SELECT 
  Id,
  SUBSTRING_INDEX(Question, '|', 1) AS Q1,
  SUBSTRING_INDEX(Reply, '|', 1) AS R1,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Question, '|', 2), '|', -1) AS Q2,
  SUBSTRING_INDEX(SUBSTRING_INDEX(Reply, '|', 2), '|', -1) AS R2
FROM 你的表名;

PostgreSQL版本 #

SELECT 
  Id,
  q_arr[1] AS Q1,
  r_arr[1] AS R1,
  q_arr[2] AS Q2,
  r_arr[2] AS R2
FROM (
  SELECT 
    Id,
    string_to_array(Question, '|') AS q_arr,
    string_to_array(Reply, '|') AS r_arr
  FROM 你的表名
) t;

SQL Server版本 #

SELECT 
  t.Id,
  MAX(CASE WHEN q.rn = 1 THEN q.value END) AS Q1,
  MAX(CASE WHEN r.rn = 1 THEN r.value END) AS R1,
  MAX(CASE WHEN q.rn = 2 THEN q.value END) AS Q2,
  MAX(CASE WHEN r.rn = 2 THEN r.value END) AS R2
FROM 你的表名 t
CROSS APPLY (
  SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
  FROM STRING_SPLIT(t.Question, '|')
) q
CROSS APPLY (
  SELECT value, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
  FROM STRING_SPLIT(t.Reply, '|')
) r
WHERE q.rn = r.rn
GROUP BY t.Id;

注意事项 #

• 若实际拆分的分隔符为逗号、换行符等，替换代码中|为对应字符即可
• 若需要拆分超过2段，可按照对应语法规则扩展Q3、R3等列的查询逻辑
• 低版本SQL Server不支持STRING_SPLIT函数，可替换为自定义拆分函数实现相同逻辑

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