T-SQL：能否无需硬编码CASE语句实现当前数据映射逻辑？

我将其他SQL生成的查询结果存储在表中，用于测试和数据清理。当前查询可实现需求：根据PiTag或FieldName列的值，将VOLUME、ENERGY、HEATING_VALUE对应值填入PiValue，保持3行数据，后续会将CONTRACT_DAY、PiTag和PiValue推送至PI时序数据库。但当前使用的CASE语句硬编码了FieldName和PiTag的值，新增字段时需修改并重新部署查询，希望找到T-SQL中更灵活的实现方式替代现有CASE逻辑。

当前查询代码：

SELECT METER_IDNUM
      ,CONTRACT_DAY
      ,VOLUME
      ,HEATING_VALUE
      ,ENERGY
      ,FieldName
      ,PiTag
      ,case when PiTag = 'ABC-Raw_Volume' then VOLUME
            when PiTag = 'ABC-Raw_Energy' then ENERGY 
            when PiTag = 'ABC-Raw_GHV' then HEATING_VALUE 
       else 0
       end as PiValue 
        
  FROM [dbo].[NealTempDemo]

数据表结构及测试数据：

CREATE TABLE [dbo].[NealTempDemo](
    [METER_IDNUM] [nvarchar](20) NOT NULL,
    [CONTRACT_DAY] [smalldatetime] NOT NULL,
    [VOLUME] [float] NULL,
    [HEATING_VALUE] [float] NULL,
    [ENERGY] [float] NULL,
    [PiTag] [nvarchar](max) NULL,
    [FieldName] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[NealTempDemo] ([METER_IDNUM], [CONTRACT_DAY], [VOLUME], [HEATING_VALUE], [ENERGY], [PiTag], [FieldName]) VALUES (N'12345', CAST(N'2023-01-01T00:00:00' AS SmallDateTime), 2325498, 1009.3034147640643, 2347136, N'ABC-Raw_Volume', N'VOLUME')
GO
INSERT [dbo].[NealTempDemo] ([METER_IDNUM], [CONTRACT_DAY], [VOLUME], [HEATING_VALUE], [ENERGY], [PiTag], [FieldName]) VALUES (N'12345', CAST(N'2023-01-01T00:00:00' AS SmallDateTime), 2325498, 1009.3034147640643, 2347136, N'ABC-Raw_Energy', N'ENERGY')
GO
INSERT [dbo].[NealTempDemo] ([METER_IDNUM], [CONTRACT_DAY], [VOLUME], [HEATING_VALUE], [ENERGY], [PiTag], [FieldName]) VALUES (N'12345', CAST(N'2023-01-01T00:00:00' AS SmallDateTime), 2325498, 1009.3034147640643, 2347136, N'ABC-Raw_GHV', N'HEATING_VALUE')
GO

方案1：利用FieldName与列名的映射简化逻辑 #

由于你的FieldName列值与目标数值列名完全匹配（例如FieldName='VOLUME'对应VOLUME列），可以用CROSS APPLY配合值对列表实现动态匹配，新增字段时只需在VALUES中追加行即可：

SELECT 
    t.METER_IDNUM,
    t.CONTRACT_DAY,
    t.VOLUME,
    t.HEATING_VALUE,
    t.ENERGY,
    t.FieldName,
    t.PiTag,
    COALESCE(v.PiValue, 0) AS PiValue
FROM [dbo].[NealTempDemo] t
CROSS APPLY (
    VALUES
        ('VOLUME', t.VOLUME),
        ('ENERGY', t.ENERGY),
        ('HEATING_VALUE', t.HEATING_VALUE)
        -- 新增字段时在此处添加(FieldName, 对应列名)即可
) v(FieldName, PiValue)
WHERE v.FieldName = t.FieldName

方案2：动态SQL自动适配新增字段 #

如果需要完全无需手动修改查询，可通过动态SQL自动读取目标列并生成匹配逻辑，新增字段后查询会自动识别：

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = '
SELECT 
    METER_IDNUM,
    CONTRACT_DAY,
    VOLUME,
    HEATING_VALUE,
    ENERGY,
    FieldName,
    PiTag,
    COALESCE(
        CASE FieldName ' + 
        STRING_AGG(
            CONCAT('WHEN ''', COLUMN_NAME, ''' THEN ', COLUMN_NAME),
            CHAR(13) + CHAR(10) + '            '
        ) + '
        END, 0
    ) AS PiValue
FROM [dbo].[NealTempDemo]
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NealTempDemo'
    AND COLUMN_NAME IN ('VOLUME', 'ENERGY', 'HEATING_VALUE') -- 可改为DATA_TYPE IN ('float')匹配所有数值列

EXEC sp_executesql @sql

方案3：配置化映射表（最灵活的可扩展方案） #

若PiTag与字段的对应关系可能变化，建议建立映射表维护对应关系，后续新增或修改映射仅需更新表，无需修改查询：

1. 创建映射表：

CREATE TABLE [dbo].[PiTagFieldMapping](
    PiTag NVARCHAR(MAX) NOT NULL PRIMARY KEY,
    FieldName NVARCHAR(MAX) NOT NULL
)

INSERT INTO [dbo].[PiTagFieldMapping] VALUES
('ABC-Raw_Volume', 'VOLUME'),
('ABC-Raw_Energy', 'ENERGY'),
('ABC-Raw_GHV', 'HEATING_VALUE')

2. 关联映射表的查询逻辑：

SELECT 
    t.METER_IDNUM,
    t.CONTRACT_DAY,
    t.VOLUME,
    t.HEATING_VALUE,
    t.ENERGY,
    t.FieldName,
    t.PiTag,
    COALESCE(
        CASE m.FieldName 
            WHEN 'VOLUME' THEN t.VOLUME
            WHEN 'ENERGY' THEN t.ENERGY
            WHEN 'HEATING_VALUE' THEN t.HEATING_VALUE
        END, 0
    ) AS PiValue
FROM [dbo].[NealTempDemo] t
JOIN [dbo].[PiTagFieldMapping] m ON t.PiTag = m.PiTag

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