SQL Server数据转换动态列选择处理的可维护性优化方案问询

需求说明 #

需要对现有表驱动的数据一致性适配逻辑做改进或替换实现，核心目标是提升代码可维护性：最优效果是实现代码自维护，也可支持基于MappingRules规则表自动生成代码。
该场景核心逻辑为：数据提取时选取的目标列，会根据行所属的流程类型动态变化。需要注意MappingRules规则表会随业务迭代持续更新，不断新增不同流程的适配规则，将对应流程的源数据按要求对齐到统一输出Schema。

现有方案及问题 #

当前共有三种可实现需求的方案，但均存在需要手动维护代码的问题：

• CASE语句方案：规则发生变更时，必须手动同步维护CASE分支的判断逻辑
• 分区+UNION ALL拼接方案：按列值分区查询后合并结果，同样需要手动维护每个分支的查询逻辑
• UNPIVOT行转列方案：将维护范围收敛到了待转换的列清单，但列清单仍然无法动态获取，需要手动维护或者额外开发代码生成逻辑

完整可运行测试代码（SQL Server） #

SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SourceData]') AND type in (N'U'))
DROP TABLE [dbo].[SourceData]
GO

-- 源数据表：存储不同流程的业务数据，同一ProcedureID下数据逻辑一致，不同流程存储设备信息的字段存在差异，需统一格式供分析使用
CREATE TABLE SourceData (
    RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    , ProcedureID INT NOT NULL
    , EquipmentSystem varchar(50) NULL
    , EquipmentDevice varchar(50) NULL
    , EquipmentName varchar(50) NULL
    -- 后续可扩展更多字段，扩展后需要同步维护适配逻辑
)
;

INSERT INTO SourceData (ProcedureID, EquipmentSystem, EquipmentDevice, EquipmentName)
VALUES (1, 'A system', 'Unused', 'Also unused')
    , (1, 'Another system', 'Unused', 'Unused')
    , (1, 'Yet another system', 'Unused', 'Unused')
    , (2, 'Unuseful data', 'Some device', 'Unused')
    , (2, 'More garbage', 'A different device', 'Unused')
    , (3, 'Not used', 'Unused', 'Model 1')
    , (3, 'Unused', 'Irrelevant', 'Model 2')
;
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MappingRules]') AND type in (N'U'))
DROP TABLE [dbo].[MappingRules]
GO

-- 映射规则表：配置不同流程ID对应的流程名称、需要提取的设备信息字段名
CREATE TABLE MappingRules (
    RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    , ProcedureID INT NOT NULL
    , ProcedureName varchar(50) NOT NULL
    , ProcedureEquipmentColumnName sysname NOT NULL
)
;

INSERT INTO MappingRules (ProcedureID, ProcedureName, ProcedureEquipmentColumnName)
VALUES (1, 'Installation', 'EquipmentSystem')
    , (2, 'Maintenance', 'EquipmentDevice')
    , (3, 'Oil change', 'EquipmentName')
;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DestinationData]') AND type in (N'U'))
DROP TABLE [dbo].[DestinationData]
GO

-- 目标表：统一格式后的输出表结构，供数据分析使用
CREATE TABLE DestinationData (
    RowID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED
    , SourceRowID INT NOT NULL
    , ProcedureName varchar(50) NOT NULL
    , ProcedureEquipment varchar(50) NULL
)
;

-- 查看源数据
SELECT *
FROM SourceData
;

-- 查看映射规则
SELECT *
FROM MappingRules
;

-- 方案1：CASE语句实现，规则变更时需要手动维护CASE分支
SELECT SourceRowID = SourceData.RowID
    , ProcedureName = MappingRules.ProcedureName
    , ProcedureEquipment =
        CASE
            WHEN MappingRules.ProcedureEquipmentColumnName = 'EquipmentSystem'
                THEN SourceData.EquipmentSystem
            WHEN MappingRules.ProcedureEquipmentColumnName = 'EquipmentDevice'
                THEN SourceData.EquipmentDevice
            WHEN MappingRules.ProcedureEquipmentColumnName = 'EquipmentName'
                THEN SourceData.EquipmentName
            ELSE
                NULL
        END
FROM SourceData
INNER JOIN MappingRules
    ON MappingRules.ProcedureID = SourceData.ProcedureID
;

-- 方案2：分区+UNION ALL拼接实现，需要手动维护每个分支的过滤条件和取值字段
SELECT SourceRowID = SourceData.RowID
    , ProcedureName = MappingRules.ProcedureName
    , ProcedureEquipment = SourceData.EquipmentSystem
FROM SourceData
INNER JOIN MappingRules
    ON MappingRules.ProcedureID = SourceData.ProcedureID
    AND MappingRules.ProcedureEquipmentColumnName = 'EquipmentSystem'
UNION ALL
SELECT SourceRowID = SourceData.RowID
    , ProcedureName = MappingRules.ProcedureName
    , ProcedureEquipment = SourceData.EquipmentDevice
FROM SourceData
INNER JOIN MappingRules
    ON MappingRules.ProcedureID = SourceData.ProcedureID
    AND MappingRules.ProcedureEquipmentColumnName = 'EquipmentDevice'
UNION ALL
SELECT SourceRowID = SourceData.RowID
    , ProcedureName = MappingRules.ProcedureName
    , ProcedureEquipment = SourceData.EquipmentName
FROM SourceData
INNER JOIN MappingRules
    ON MappingRules.ProcedureID = SourceData.ProcedureID
    AND MappingRules.ProcedureEquipmentColumnName = 'EquipmentName'
;

-- 方案3：UNPIVOT行转列实现，列清单无法动态获取，需要手动维护或通过代码生成
WITH unpivoted AS (
    SELECT RowID
        , ProcedureID
        , ColumnName
        , ColumnValue
    FROM SourceData
    UNPIVOT (
        ColumnValue FOR ColumnName IN (
            EquipmentSystem
            , EquipmentDevice
            , EquipmentName
        )
    ) AS unpvt
)
SELECT SourceRowID = unpivoted.RowID
    , ProcedureName = MappingRules.ProcedureName
    , ProcedureEquipment = unpivoted.ColumnValue
FROM unpivoted
INNER JOIN MappingRules
    ON MappingRules.ProcedureID = unpivoted.ProcedureID
    AND MappingRules.ProcedureEquipmentColumnName = unpivoted.ColumnName
;

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