如何基于给定3张表生成规范化PROGRAM表？求SQL技术指导

我持有Program、Companies、People三张业务表的结构、测试数据及对应的SQL建表与插入语句，需要生成指定格式的规范化PROGRAM表。已编写部分SQL脚本，但无法得到预期结果，恳请专业人士提供SQL实现指导，相关信息如下：

业务表结构及数据 #

TABLE1: PROGRAM #

TABLE2: COMPANY #

TABLE3: PEOPLE #

建表与插入SQL语句 #

create table Program
(
    Prgm_name varchar(225),
    Company varchar(500),
    People varchar(500)
)

create table Companies
(
    Company_name varchar(255),
    Program varchar(500),
    People varchar(500)
)

create table People
(
    Full_name varchar(225),
    Company varchar(500),
    program varchar(500)
)

insert into program values ('A', 'X CO., Y CO.', 'JOHN')
insert into program values ('B', 'M CO., N CO., O CO.', 'KRISH, BIKRAM, TOM')

insert into Companies values ('X CO.','A','JOHN')
insert into Companies values ('Y CO.','A','')
insert into Companies values ('M CO.','B','')
insert into Companies values ('N CO.','B','KRISH, TOM')
insert into Companies values ('O CO.','B','')

insert into People values ('JOHN','X CO.','A')
insert into People values ('BIKRAM','','B')
insert into People values ('KRISH','N CO.','B')
insert into People values ('TOM','N CO.','B')
insert into People values ('LYNN','N CO.','')

SELECT * FROM Program
SELECT * FROM Companies
SELECT * FROM people

预期结果 #

基于上述三张表，我需要生成如下格式的规范化PROGRAM表：

现有脚本 #

select * 
into #program 
from
    (select 
         a.PRGM_NAME as PRGM_NAME_P, a.COMPANY AS COMPANY_P, 
         a.PEOPLE AS PEOPLE_P, c.Full_name AS FULL_NAME_PP,
         c.Company AS COMPANY_PP, c.program AS PROGRAM_PP 
     from 
         (select distinct b.PRGM_NAME, x.COMPANY, Y.PEOPLE
          from 
              (select PRGM_NAME, COMPANY, PEOPLE from Program) b
          cross apply
              (select trim(value) from string_split(b.COMPANY, ',')) x (COMPANY) 
          cross apply 
              (select trim(value) from string_split(b.PEOPLE, ',')) Y (PEOPLE))a
     left join
         people c on a.Prgm_name = c.program AND A.COMPANY = C.Company) d


SELECT
    PRGM_NAME_P, COMPANY_P AS COMPANY_P_Y, PEOPLE_P, 
    FULL_NAME_PP, COMPANY_PP, PROGRAM_PP,
    CASE
        WHEN COMPANY_P <> '' AND COMPANY_PP = '' THEN FULL_NAME_PP
        WHEN COMPANY_P <> COMPANY_PP THEN ''
        ELSE FULL_NAME_PP
    END FULL_NAME_Y,
    ROW_NUMBER () OVER (PARTITION BY PRGM_NAME_P, PEOPLE_P 
                        ORDER BY COMPANY_PP DESC ) AS RANK1
FROM 
    #program

解决方案 #

要得到预期的规范化表，需分别处理关联人员的公司、无关联人员的公司、无关联公司的人员三类数据，再合并结果：

-- 1. 拆分Program表的逗号分隔字段，得到基础拆分数据
WITH SplitProgram AS (
    SELECT 
        p.Prgm_name,
        TRIM(c.value) AS Company,
        TRIM(person.value) AS People
    FROM Program p
    CROSS APPLY STRING_SPLIT(p.Company, ',') c
    CROSS APPLY STRING_SPLIT(p.People, ',') person
),
-- 2. 匹配公司与人员的关联记录
CompanyPeopleMatches AS (
    SELECT DISTINCT
        sp.Prgm_name,
        sp.Company,
        sp.People
    FROM SplitProgram sp
    JOIN People pe ON sp.Prgm_name = pe.program 
        AND sp.Company = pe.Company
        AND sp.People = pe.Full_name
),
-- 3. 提取无对应人员的公司记录
EmptyCompanyEntries AS (
    SELECT DISTINCT
        p.Prgm_name,
        TRIM(c.value) AS Company,
        '' AS People
    FROM Program p
    CROSS APPLY STRING_SPLIT(p.Company, ',') c
    WHERE NOT EXISTS (
        SELECT 1 FROM People pe 
        WHERE p.Prgm_name = pe.program 
          AND TRIM(c.value) = pe.Company
    )
),
-- 4. 提取无对应公司的人员记录
EmptyPersonEntries AS (
    SELECT DISTINCT
        pe.program AS Prgm_name,
        '' AS Company,
        pe.Full_name AS People
    FROM People pe
    WHERE pe.Company = '' 
      AND EXISTS (
          SELECT 1 FROM Program p 
          WHERE p.Prgm_name = pe.program
            AND CHARINDEX(pe.Full_name, p.People) > 0
      )
)
-- 合并所有结果并排序
SELECT * FROM CompanyPeopleMatches
UNION ALL
SELECT * FROM EmptyCompanyEntries
UNION ALL
SELECT * FROM EmptyPersonEntries
ORDER BY Prgm_name, 
         CASE WHEN Company = '' THEN 1 ELSE 0 END, 
         People;

脚本说明 #

1. SplitProgram：拆分Program表中逗号分隔的公司和人员字段，生成基础拆分数据集。
2. CompanyPeopleMatches：将拆分数据与People表关联，筛选出公司和人员完全匹配的记录。
3. EmptyCompanyEntries：筛选Program表中没有对应人员的公司记录。
4. EmptyPersonEntries：筛选People表中属于目标项目但无关联公司的人员记录。
5. 通过UNION ALL合并三类数据，按项目名称、公司是否为空、人员名称排序，得到预期的规范化表。

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