SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- The source data for a variety of events that are consistent within themselves for a particular ProcedureID, but may vary between procedures and we're trying to conform them for easier analysis
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
	-- ... Arbitrarily more possible columns that will need logic to be maintained
)
;

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')
;

-- Table of rules for which column we want to select for different types of procedures
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')
;

-- Schema for what we'll eventually be having available for analytics
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
;

-- One technique using a CASE statement which will have to be maintained
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
;

-- Another technique partitioning the space first, and then using UNION to bring them all back together
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'
;

RowID	ProcedureID	EquipmentSystem	EquipmentDevice	EquipmentName
1	1	A system	Unused	Also unused
2	1	Another system	Unused	Unused
3	1	Yet another system	Unused	Unused
4	2	Unuseful data	Some device	Unused
5	2	More garbage	A different device	Unused
6	3	Not used	Unused	Model 1
7	3	Unused	Irrelevant	Model 2

RowID	ProcedureID	ProcedureName	ProcedureEquipmentColumnName
1	1	Installation	EquipmentSystem
2	2	Maintenance	EquipmentDevice
3	3	Oil change	EquipmentName

SourceRowID	ProcedureName	ProcedureEquipment
1	Installation	A system
2	Installation	Another system
3	Installation	Yet another system
4	Maintenance	Some device
5	Maintenance	A different device
6	Oil change	Model 1
7	Oil change	Model 2

SourceRowID	ProcedureName	ProcedureEquipment
1	Installation	A system
2	Installation	Another system
3	Installation	Yet another system
4	Maintenance	Some device
5	Maintenance	A different device
6	Oil change	Model 1
7	Oil change	Model 2