T-SQL - Can I do this without hard-coding the Case Statement

I have other SQL that results in a query which I stored in a table for testing and sanitizing the data. The following query accomplishes what I want to do for this specific case (i.e. continue to have 3 rows of data with the PiValue filled in properly from other columns, based on the value of the PiTag and or the FieldName column). I will be sending CONTRACT_DAY, PiTag and PiValue to a timeseries database called PI (PiSystem) using other tools.

Note that my case statement is hard-coding the values in the FieldName and PiTag column. So if they ever added a new field, I would have to change, and redeploy my query. So I'm wondering if there is a clever way in T-SQL to do this without then "case" logic that I have provided. I was thinking maybe "pivot" could help.

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]

I put it on SQLFiddle here: http://sqlfiddle.com/#!18/94803/1

Create data SQL is:

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

The following will convert the entire current row to XML and then use the FieldName value to select the desired column element to extract the value.

SELECT METER_IDNUM
      ,CONTRACT_DAY
      ,VOLUME
      ,HEATING_VALUE
      ,ENERGY
      ,FieldName
      ,PiTag
      ,X.XML.value('(*[local-name()=sql:column("D.FieldName")]/text())[1]', 'float') AS PiValue
      --,X.XML
FROM NealTempDemo D
CROSS APPLY (
    SELECT D.*
    FOR XML PATH(''), TYPE
) AS X(XML);

The above assumes that all selected values are convertible to FLOAT.

Results:

See this db<>fiddle for a working demo.

Caution Just be aware that this approach may add costly overhead to your query - especially if your data contains "dozens of other columns". If your data is coming from several different sources joined together, you might want to look at applying the XML operation at a point closer to the source where data bloat isn't as bad.