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

-- Dynamic using xml
SELECT 
    SourceRowID = SD.RowID,
    MR.ProcedureName,
    ProcedureEquipment = (SELECT SD.* FOR XML PATH, TYPE).value(
        '(row/*[local-name()=sql:column("MR.ProcedureEquipmentColumnName")])[1]/text()[1]', 
        'sysname') 
FROM dbo.MappingRules AS MR
JOIN dbo.SourceData AS SD
    ON SD.ProcedureID = MR.ProcedureID;
    
-- Dynamic unpivot
DECLARE 
    @SQL nvarchar(max) =
        N'
        SELECT 
            SourceRowID = U.RowID, 
            MR.ProcedureName,
            ProcedureEquipment = U.CValue
        FROM dbo.SourceData AS SD
        UNPIVOT (CValue FOR CName IN (@ColumnList)) AS U
        JOIN dbo.MappingRules AS MR
            ON MR.ProcedureID = U.ProcedureID
            AND MR.ProcedureEquipmentColumnName = U.CName;
        ',
    @ColumnList nvarchar(max) =
    (
        SELECT
            STRING_AGG(QUOTENAME(C.[name]), ',')
        FROM sys.objects AS O
        JOIN sys.schemas AS S
            ON S.[schema_id] = O.[schema_id]
        JOIN sys.columns AS C
            ON C.[object_id] = O.[object_id]
        WHERE
            S.[name] = N'dbo'
            AND O.[name] = N'SourceData'
            AND C.[name] NOT IN (N'RowID', N'ProcedureID')
    );

SET @SQL = REPLACE(@SQL, N'@ColumnList', @ColumnList);

--PRINT @SQL;
EXECUTE (@SQL);

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

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