sql - 处理数据转换规则中的动态列选择的技术

Question

我正在寻找以下技术的改进/替代方案，这些技术会产生更易于维护的代码(如果可能，可以 self 维护，或者可能是从 MappingRules 表生成代码)来执行表驱动的数据一致性，其中列我们要使用因行类型而异。

假设 MappingRules 表会随着时间的推移而改变，以添加新的过程，以便能够根据需要使它们的数据符合输出架构。

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

我还将添加我过去使用的 UNPIVOT 技术，以将维护限制在列列表中:

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

Answer 1

有趣的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;

输出:

<表类="s-表"><头>SourceRowID过程名称程序设备<正文>1安装一个系统2安装另一个系统3安装又一个系统4维护一些设备5维护不同的设备6换油模型 17换油模型 2

想法是将源数据转换为每一行的 xml，然后提取名称与 ProcedureEquipmentColumnName 匹配的元素的文本。

动态逆轴

更实际一点，我可能会使用动态 SQL 来生成逆透视查询:

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