SQL для преобразования SCD, хранящегося в виде пар ключ-значение, в столбцы с историей

Вы можете попробовать это. PIVOT и некоторые оконные функции могут решить вашу проблему.

SELECT 
 StartDate,
 EndDate,
 ID,
 ISNULL([Name], FIRST_VALUE([Name]) OVER(PARTITION BY ID ORDER BY StartDate))  AS [Name], 
 ISNULL([Status], FIRST_VALUE([Status]) OVER(PARTITION BY ID ORDER BY StartDate))  AS [Status], 
 ISNULL([Group], FIRST_VALUE([Group]) OVER(PARTITION BY ID ORDER BY StartDate))   AS [Group], 
 ISNULL([Sub Group], FIRST_VALUE([Sub Group]) OVER(PARTITION BY ID ORDER BY StartDate))AS [Sub Group]
FROM (
    SELECT StartDate, 
        ISNULL(EndDate,  MAX(EndDate) OVER(PARTITION BY StartDate,ID )) AS EndDate,
        ID, Dimension, Value 
    FROM MyTable
) SRC
PIVOT(MAX( Value) FOR Dimension IN ([Name], [Status], [Group], [Sub Group])) PVT
order by ID desc, StartDate

Динамическая версия:

DECLARE @Columns NVARCHAR(MAX) =''
DECLARE @PivotIn NVARCHAR(MAX) =''

SELECT 
  @PivotIn = CONCAT(@PivotIn ,', ', QUOTENAME(Dimension))
, @Columns = CONCAT(@Columns , ', ', 'ISNULL(',QUOTENAME(Dimension),', FIRST_VALUE(',QUOTENAME(Dimension),') OVER(PARTITION BY ID ORDER BY StartDate))  AS ',QUOTENAME(Dimension),'')
FROM ( SELECT DISTINCT Dimension FROM MyTable   ) AS X

DECLARE @SqlQuery NVARCHAR(MAX) = 'SELECT 
    StartDate,
    EndDate,
    ID ' + 
    @Columns 
    +' FROM 
    (
        SELECT StartDate, 
            ISNULL(EndDate,  MAX(EndDate) OVER(PARTITION BY StartDate,ID )) AS EndDate,
            ID, Dimension, Value 
        FROM MyTable

    ) SRC
    PIVOT(MAX( Value) FOR Dimension IN (' +  STUFF(@PivotIn,1,1,'') + ')) PVT
    ORDER BY ID DESC, StartDate'


EXEC sp_executesql @SqlQuery