Techniques for handling dynamic column choices in data transformation rules

Question

I'm looking for improvements/alternatives to the techniques below which result in more maintainable code (self-maintainable if possible, or maybe code-generated from the MappingRules table) to do this table-driven conformance of data where the columns we want to use varies by type of row.

Assume that the MappingRules table would change over time to add new procedures to be able to conform their data as desired to the output schema.

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

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

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

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

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

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=16ce1e02cae1e6916b403fe9143d478b

I'll also add an UNPIVOT technique I have used in the past to confine maintenance to a list of columns:

-- UNPIVOT-based technique (list of columns cannot really be dynamic, but would have to be maintained/code-generated)
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
;

user_15961388 · Answer

Fun xml solution

I can't say this will be the last word in efficiency, but it is fully dynamic:

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;

Output:

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

The idea is to convert the source data into xml for each row, then extract the text of the element with the name matching ProcedureEquipmentColumnName.

Dynamic unpivot

More practically, I would probably use dynamic SQL to generate an unpivot query:

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]), N',')
        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);

db<>fiddle online demo

Techniques for handling dynamic column choices in data transformation rules

1 Answers

Fun xml solution

Dynamic unpivot