如何基于Rules映射表动态生成符合要求的SQL查询结果？

先给出示例表变量的测试数据，方便复现场景：

DECLARE @TableA TABLE (Main_ID INT, ColA VARCHAR(50), ColB VARCHAR(50));
DECLARE @TableB TABLE (Main_ID INT, ColC VARCHAR(50));
DECLARE @Rules TABLE (Header_Name VARCHAR(50), TableColumn VARCHAR(50));

INSERT INTO @TableA VALUES (1, 'A1', 'B1'), (2, 'A2', 'B2');
INSERT INTO @TableB VALUES (1, 'C1'), (2, 'C2');
INSERT INTO @Rules VALUES 
('Header A', 'TableA.ColA'),
('Header B', 'TableA.ColB'),
('Header C', 'TableB.ColC');

问题原因 #

直接关联Rules表与数据会产生笛卡尔积，每个Main_ID会对应Rules中的每一行，导致结果行数翻倍（行数=Main_ID数量 × Rules行数）。要实现每个Main_ID一行、列对应Header的需求，需要将行数据转置为列，常用两种方案：

方案一：静态条件聚合（适合Header固定的场景） #

如果Rules表的映射关系不会频繁变动，可以直接关联数据表并指定列别名：

SELECT 
    a.Main_ID,
    a.ColA AS [Header A],
    a.ColB AS [Header B],
    b.ColC AS [Header C]
FROM @TableA a
INNER JOIN @TableB b ON a.Main_ID = b.Main_ID;

若需要严格关联Rules表（比如后续有小范围调整），可以用带Rules的条件聚合：

SELECT 
    COALESCE(a.Main_ID, b.Main_ID) AS Main_ID,
    MAX(CASE WHEN r.Header_Name = 'Header A' THEN a.ColA END) AS [Header A],
    MAX(CASE WHEN r.Header_Name = 'Header B' THEN a.ColB END) AS [Header B],
    MAX(CASE WHEN r.Header_Name = 'Header C' THEN b.ColC END) AS [Header C]
FROM @Rules r
LEFT JOIN @TableA a ON r.TableColumn IN ('TableA.ColA', 'TableA.ColB')
LEFT JOIN @TableB b ON r.TableColumn = 'TableB.ColC'
GROUP BY COALESCE(a.Main_ID, b.Main_ID);

方案二：动态SQL（适合Rules映射经常变动的场景） #

如果Rules表的Header或数据来源可能随时修改，用动态SQL自动生成查询语句更灵活：

DECLARE @SQL NVARCHAR(MAX);

-- 1. 动态生成列映射部分
SELECT @SQL = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN r.Header_Name = ''', r.Header_Name, ''' THEN ',
        -- 替换表名为别名，适配JOIN后的表引用
        REPLACE(REPLACE(r.TableColumn, 'TableA', 'a'), 'TableB', 'b'),
        ' END) AS ', QUOTENAME(r.Header_Name)
    ),
    ', '
)
FROM @Rules r;

-- 2. 转存表变量为临时表（动态SQL无法直接访问外层表变量）
SELECT * INTO #TableA FROM @TableA;
SELECT * INTO #TableB FROM @TableB;
SELECT * INTO #Rules FROM @Rules;

-- 3. 拼接完整查询语句
SET @SQL = CONCAT(
    'SELECT COALESCE(a.Main_ID, b.Main_ID) AS Main_ID, ',
    @SQL,
    ' FROM #Rules r
      LEFT JOIN #TableA a ON r.TableColumn LIKE ''TableA.%''
      LEFT JOIN #TableB b ON r.TableColumn LIKE ''TableB.%''
      GROUP BY COALESCE(a.Main_ID, b.Main_ID)'
);

-- 4. 执行动态SQL
EXEC sp_executesql @SQL;

-- 5. 清理临时表
DROP TABLE #TableA, #TableB, #Rules;

这个方案会自动读取Rules表的配置生成对应的列，无需手动修改查询语句。

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