如何在SQL中将逗号分隔的列数据拆分为多行记录？

你的原始实现虽然能得到预期结果，但硬编码了每个具体的Affiliation值，扩展性极差——新增值就得修改CTE和Union语句，维护成本很高。以下是针对不同SQL数据库的通用优化方案：

PostgreSQL #

利用string_to_array将字符串转为数组，再用unnest展开为多行：

SELECT 
  Person,
  unnest(string_to_array(Affiliation, ', ')) AS Affiliation
FROM UP;

MySQL 8.0+ #

方法1：JSON_TABLE转换 #

通过将字符串转为JSON数组，再用JSON_TABLE拆分：

SELECT 
  u.Person,
  j.Affiliation
FROM UP u
JOIN JSON_TABLE(
  CONCAT('["', REPLACE(u.Affiliation, ', ', '","'), '"]'),
  '$[*]' COLUMNS (Affiliation VARCHAR(255) PATH '$')
) j;

方法2：递归CTE拆分 #

适合无法使用JSON函数的场景：

WITH RECURSIVE split_data AS (
  SELECT 
    Person,
    Affiliation AS remaining,
    SUBSTRING_INDEX(Affiliation, ', ', 1) AS Affiliation
  FROM UP
  WHERE Affiliation IS NOT NULL AND Affiliation != ''
  UNION ALL
  SELECT 
    Person,
    SUBSTRING(remaining, LOCATE(', ', remaining) + 2),
    SUBSTRING_INDEX(SUBSTRING(remaining, LOCATE(', ', remaining) + 2), ', ', 1)
  FROM split_data
  WHERE LOCATE(', ', remaining) > 0
)
SELECT Person, Affiliation
FROM split_data;

SQL Server 2016+ #

使用官方内置的STRING_SPLIT函数，搭配CROSS APPLY关联拆分结果：

SELECT 
  u.Person,
  s.value AS Affiliation
FROM UP u
CROSS APPLY STRING_SPLIT(u.Affiliation, ', ');

Oracle #

结合REGEXP_SUBSTR和CONNECT BY递归拆分：

SELECT 
  Person,
  TRIM(REGEXP_SUBSTR(Affiliation, '[^,]+', 1, LEVEL)) AS Affiliation
FROM UP
CONNECT BY 
  LEVEL <= REGEXP_COUNT(Affiliation, ',') + 1
  AND PRIOR Person = Person
  AND PRIOR SYS_GUID() IS NOT NULL;

以上方案的核心优势是无需硬编码具体的Affiliation值，无论列中新增多少个逗号分隔的内容，都能自动完成拆分，大幅降低维护成本。

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