如何在SQL Server中将含多课程的列拆分至独立关联表

完全可以通过SQL Server内置函数实现这个需求，无需编写外部程序。以下是具体实现步骤和代码：

原表结构与数据 #

实现步骤 #

1. 创建新的program_details表（保留核心字段） #

先创建仅包含所需字段的新表，避免直接修改原表带来的数据风险：

CREATE TABLE new_program_details (
    program_code INT PRIMARY KEY,
    start_date DATE,
    active BIT
);

2. 创建program_course_mapping关联表 #

用于存储项目与课程的对应关系：

CREATE TABLE program_course_mapping (
    mapping_id INT IDENTITY(1,1) PRIMARY KEY,
    pgm_code INT FOREIGN KEY REFERENCES new_program_details(program_code),
    course_id VARCHAR(50)
);

3. 向新program_details表插入数据 #

从原表提取需要保留的字段数据：

INSERT INTO new_program_details (program_code, start_date, active)
SELECT program_code, start_date, active
FROM program_details;

4. 拆分course_available字段并插入关联表 #

使用SQL Server 2016及以上版本支持的STRING_SPLIT函数拆分分号分隔的课程ID，同时过滤可能的空值：

INSERT INTO program_course_mapping (pgm_code, course_id)
SELECT 
    pd.program_code,
    TRIM(s.value) AS course_id
FROM program_details pd
CROSS APPLY STRING_SPLIT(pd.course_available, ';') s
WHERE TRIM(s.value) <> '';

5. 替换原表（可选） #

确认数据无误后，可将原表重命名为备份表，再把新表改为原表名：

-- 重命名原表为备份表
EXEC sp_rename 'program_details', 'program_details_backup';
-- 重命名新表为原表名
EXEC sp_rename 'new_program_details', 'program_details';

拆分后结果 #

新program_details表 #

program_course_mapping表（部分数据） #

兼容低版本说明：如果你的SQL Server版本低于2016，STRING_SPLIT不可用，可使用XML拆分方式替代：

INSERT INTO program_course_mapping (pgm_code, course_id)
SELECT 
    pd.program_code,
    TRIM(Split.a.value('.', 'VARCHAR(100)')) AS course_id
FROM (
    SELECT 
        program_code,
        CAST('<M>' + REPLACE(course_available, ';', '</M><M>') + '</M>' AS XML) AS Data
    FROM program_details
) AS pd
CROSS APPLY Data.nodes('/M') AS Split(a)
WHERE TRIM(Split.a.value('.', 'VARCHAR(100)')) <> '';

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