如何优雅关联维度数量可变的维度表与键表？

问题背景 #

现有两张业务表：

1. dimension_table：存储Lookup Table的维度索引映射信息，结构与数据如下：

2. cells_table：存储维度组合对应的数值，支持1-15个维度，结构与数据如下：

需求：将两张表关联，生成可读性强的维度值+数值输出，需适配任意Lookup Table，且支持1-15个任意数量的维度，示例输出如下：

现有方案的局限 #

当前使用固定维度的CTE+多JOIN方式，需要手动为每个维度添加JOIN语句，维度数量变更时（如从3维改为5维）需修改SQL，扩展性极差：

WITH Dim As (
   SELECT Dimension, Key, Value
   FROM dimension_table
)

SELECT d1.Value AS Dimension1, d2.Value AS Dimension2, d3.Value AS Dimension3, c.Value
FROM   cells_table c
JOIN   Dim d1
ON     d1.Key = c.Dimension1Key
AND    d1.Dimension = 1
JOIN   Dim d2
ON     d2.Key = c.Dimension2Key
AND    d2.Dimension = 2
JOIN   Dim d3
ON     d3.Key = c.Dimension3Key
AND    d3.Dimension = 3

通用解决方案 #

方案1：UNPIVOT+PIVOT转换（适用于SQL Server、Oracle等支持该语法的数据库） #

通过将cells_table的宽表结构转成行，关联维度表后再转回宽表，实现通用适配：

-- 1. 将cells_table的维度列转为行结构
WITH CellsUnpivoted AS (
    SELECT 
        [Lookup Table] AS LookupTable,
        -- 提取维度编号
        CAST(SUBSTRING(DimensionCol, 9, 2) AS INT) AS Dimension,
        DimensionKey,
        Value AS CellValue
    FROM cells_table
    UNPIVOT (
        DimensionKey FOR DimensionCol IN (
            Dimension1Key, Dimension2Key, Dimension3Key,
            Dimension4Key, Dimension5Key, Dimension6Key,
            Dimension7Key, Dimension8Key, Dimension9Key,
            Dimension10Key, Dimension11Key, Dimension12Key,
            Dimension13Key, Dimension14Key, Dimension15Key
        )
    ) AS unpvt
    -- 过滤未使用的NULL维度键
    WHERE DimensionKey IS NOT NULL
),
-- 2. 关联维度表获取维度值
DimJoined AS (
    SELECT 
        cu.LookupTable,
        cu.Dimension,
        dt.Value AS DimensionValue,
        cu.CellValue
    FROM CellsUnpivoted cu
    JOIN dimension_table dt
        ON cu.LookupTable = dt.[Lookup Table]
        AND cu.Dimension = dt.Dimension
        AND cu.DimensionKey = dt.Key
)
-- 3. 将行转回宽表结构，生成最终输出
SELECT 
    [1] AS Dimension1,
    [2] AS Dimension2,
    [3] AS Dimension3,
    [4] AS Dimension4,
    [5] AS Dimension5,
    [6] AS Dimension6,
    [7] AS Dimension7,
    [8] AS Dimension8,
    [9] AS Dimension9,
    [10] AS Dimension10,
    [11] AS Dimension11,
    [12] AS Dimension12,
    [13] AS Dimension13,
    [14] AS Dimension14,
    [15] AS Dimension15,
    CellValue AS Value
FROM DimJoined
PIVOT (
    MAX(DimensionValue)
    FOR Dimension IN (
        [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
        [11],[12],[13],[14],[15]
    )
) AS pvt
-- 可按需过滤特定LookupTable
WHERE LookupTable = 'Products';

方案2：动态SQL生成（适用于大多数数据库） #

通过自动获取当前LookupTable的维度数量，动态生成对应的JOIN和SELECT语句，彻底摆脱固定维度限制：

以SQL Server为例，动态SQL代码：

DECLARE @LookupTable NVARCHAR(100) = 'Products';
DECLARE @SelectColumns NVARCHAR(MAX) = '';
DECLARE @JoinStatements NVARCHAR(MAX) = '';
DECLARE @MaxDimension INT;

-- 获取当前LookupTable使用的最大维度编号
SELECT @MaxDimension = MAX(Dimension)
FROM dimension_table
WHERE [Lookup Table] = @LookupTable;

-- 生成SELECT列和JOIN语句
WITH DimNumbers AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1 FROM DimNumbers WHERE Num < @MaxDimension
)
SELECT 
    @SelectColumns += CONCAT('d', Num, '.Value AS Dimension', Num, ', '),
    @JoinStatements += CONCAT(
        'JOIN dimension_table d', Num, '
         ON c.Dimension', Num, 'Key = d', Num, '.Key
         AND d', Num, '.Dimension = ', Num, '
         AND d', Num, '.[Lookup Table] = c.[Lookup Table]
         '
    )
FROM DimNumbers;

-- 拼接最终SQL语句
SET @SelectColumns = LEFT(@SelectColumns, LEN(@SelectColumns) - 2) + ', c.Value';
DECLARE @FinalSQL NVARCHAR(MAX) = CONCAT(
    'SELECT ', @SelectColumns, '
     FROM cells_table c
     ', @JoinStatements, '
     WHERE c.[Lookup Table] = ''', @LookupTable, ''''
);

-- 执行动态SQL
EXEC sp_executesql @FinalSQL;

该方案会自动适配dimension_table中当前LookupTable的维度数量，无需手动修改SQL代码。

内容的提问来源于stack exchange，提问作者Alphabetwolf