如何将多对多关联表数据合并为单列生成目标SQL视图？

完全可行，XML PATH是兼容多数老版本SQL环境的经典方法，无需动态SQL即可实现需求。以下是具体实现步骤和代码：

基于原始表的实现 #

如果直接从三张关联表生成目标视图，可使用以下SQL：

CREATE VIEW view_med_combined_substances
AS
SELECT
    m.ID_med,
    m.Med_Name, -- 替换为药品表实际的名称/其他字段
    -- 合并关联物质ID为逗号分隔字符串
    ISNULL(STUFF((
        SELECT ',' + CAST(s.ID_Sub AS VARCHAR(MAX))
        FROM tbl_connection c
        INNER JOIN tbl_sub s ON c.ID_sub = s.ID_Sub
        WHERE c.ID_med = m.ID_med
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '') AS present_id_subs,
    -- 合并关联物质名称为逗号分隔字符串
    ISNULL(STUFF((
        SELECT ',' + s.Sub_Name -- 替换为物质表实际的名称字段
        FROM tbl_connection c
        INNER JOIN tbl_sub s ON c.ID_sub = s.ID_Sub
        WHERE c.ID_med = m.ID_med
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '') AS present_name_subs
FROM tbl_med m
GROUP BY m.ID_med, m.Med_Name; -- 所有非聚合的药品字段必须加入GROUP BY

基于现有视图的简化实现 #

如果view_meds_stock_subs_present已经包含药品与对应物质的关联数据，可直接基于该视图生成：

CREATE VIEW view_med_combined_substances
AS
SELECT
    ID_med,
    Med_Name, -- 替换为视图中实际的药品字段
    ISNULL(STUFF((
        SELECT ',' + CAST(ID_Sub AS VARCHAR(MAX))
        FROM view_meds_stock_subs_present s
        WHERE s.ID_med = m.ID_med
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '') AS present_id_subs,
    ISNULL(STUFF((
        SELECT ',' + Sub_Name
        FROM view_meds_stock_subs_present s
        WHERE s.ID_med = m.ID_med
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, ''), '') AS present_name_subs
FROM view_meds_stock_subs_present m
GROUP BY ID_med, Med_Name;

关键说明 #

• STUFF函数用于移除拼接字符串开头多余的逗号
• FOR XML PATH(''), TYPE将子查询结果拼接为XML格式，再通过.value()转换为普通字符串，避免特殊字符（如&、<）被转义
• ISNULL用于处理无关联物质的药品，返回空字符串而非NULL
• 所有SELECT中未参与聚合的字段必须加入GROUP BY子句

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