Zapytanie wyszczególniające różnice między wierszami dla dużej ilości danych

Mam wiele dużych tabel, każda z> 300 kolumnami. Aplikacja, której używam, tworzy „archiwa” zmienionych wierszy, tworząc kopię bieżącego wiersza w tabeli pomocniczej.

Rozważ prosty przykład:

CREATE TABLE dbo.bigtable
(
  UpdateDate datetime,
  PK varchar(12) PRIMARY KEY,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Tabela archiwów:

CREATE TABLE dbo.bigtable_archive
(
  UpdateDate datetime,
  PK varchar(12) NOT NULL,
  col1 varchar(100),
  col2 int,
  col3 varchar(20),
  .
  .
  .
  colN datetime
);

Przed aktualizacje są przeprowadzane dbo.bigtable, jest tworzona kopia w wierszu dbo.bigtable_archive, a następniedbo.bigtable.UpdateDate aktualizowana o bieżącą datę.

Dlatego UNIONpołączenie dwóch tabel i grupowanie według PKtworzy oś czasu zmian, gdy są uporządkowane wedługUpdateDate .

Chcę utworzyć raport wyszczególniający różnice między wierszami, uporządkowane według UpdateDate, pogrupowane według PK, w następującym formacie:

PK,   UpdateDate,  ColumnName,  Old Value,   New Value

Old Valuei New Valuemogą być odpowiednie kolumny rzutowane na a VARCHAR(MAX)(nie ma TEXTlubBYTE zaangażowane kolumny kolumny), ponieważ nie muszę samodzielnie przetwarzać wartości.

W tej chwili nie mogę wymyślić rozsądnego sposobu na zrobienie tego dla dużej liczby kolumn bez uciekania się do generowania zapytań programowo - być może będę musiał to zrobić.

Otwarty na wiele pomysłów, więc dodam nagrodę do pytania po 2 dniach.

Nie będzie to ładnie wyglądać, zwłaszcza biorąc pod uwagę ponad 300 kolumn i niedostępność LAG, ani też nie będzie działało wyjątkowo dobrze, ale tak na początek, spróbuję zastosować następujące podejście:

• UNION dwa stoły.
• Dla każdej PK w połączonym zestawie uzyskaj jej poprzednie „wcielenie” z tabeli archiwum (implementacja poniżej używa OUTER APPLY+ TOP (1)jako biednego człowieka LAG).
• Rzuć każdą kolumnę danych varchar(max)i rozdziel ją w parach, tj. Bieżąca i poprzednia wartość ( CROSS APPLY (VALUES ...)działa dobrze dla tej operacji).
• Na koniec odfiltruj wyniki na podstawie tego, czy wartości w każdej parze różnią się od siebie.

Transact-SQL powyższego, jak go widzę:

WITH
  Combined AS
  (
    SELECT * FROM dbo.bigtable
    UNION ALL
    SELECT * FROM dbo.bigtable_archive
  ) AS derived,
  OldAndNew AS
  (
    SELECT
      this.*,
      OldCol1 = last.Col1,
      OldCol2 = last.Col2,
      ...
    FROM
      Combined AS this
      OUTER APPLY
      (
        SELECT TOP (1)
          *
        FROM
          dbo.bigtable_archive
        WHERE
          PK = this.PK
          AND UpdateDate < this.UpdateDate
        ORDER BY
          UpdateDate DESC
      ) AS last
  )
SELECT
  t.PK,
  t.UpdateDate,
  x.ColumnName,
  x.OldValue,
  x.NewValue
FROM
  OldAndNew AS t
  CROSS APPLY
  (
    VALUES
    ('Col1', CAST(t.OldCol1 AS varchar(max), CAST(t.Col1 AS varchar(max))),
    ('Col2', CAST(t.OldCol2 AS varchar(max), CAST(t.Col2 AS varchar(max))),
    ...
  ) AS x (ColumnName, OldValue, NewValue)
WHERE
  NOT EXISTS (SELECT x.OldValue INTERSECT x.NewValue)
ORDER BY
  t.PK,
  t.UpdateDate,
  x.ColumnName
;

Jeśli cofniesz przestawienie danych do tabeli tymczasowej

create table #T
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  ColumnName nvarchar(128) not null,
  Value varchar(max),
  Version int not null
);

Możesz dopasować wiersze, aby znaleźć nową i starą wartość z włączeniem własnym PK, ColumnNamei Version = Version + 1.

Nie taka ładna część polega oczywiście na przestawieniu 300 kolumn do tabeli tymczasowej z dwóch tabel podstawowych.

XML na ratunek, aby wszystko było mniej niezręczne.

Możliwe jest cofnięcie przestawienia danych za pomocą XML bez konieczności dowiedzenia się, jakie rzeczywiste kolumny w tabeli zostaną rozproszone. Nazwy kolumn muszą być poprawne jako nazwy elementów w języku XML, inaczej nie powiedzie się.

Pomysł polega na utworzeniu jednego kodu XML dla każdego wiersza zawierającego wszystkie wartości dla tego wiersza.

select bt.PK,
       bt.UpdateDate,
       (select bt.* for xml path(''), elements xsinil, type) as X
from dbo.bigtable as bt;

<UpdateDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</UpdateDate>
<PK xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">PK1</PK>
<col1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">c1_1_3</col1>
<col2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">3</col2>
<col3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<colN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</colN>

elements xsiniljest tam, aby utworzyć elementy dla kolumn z NULL.

XML można następnie zniszczyć za pomocą, nodes('*') aby uzyskać jeden wiersz dla każdej kolumny i użyć local-name(.)do uzyskania nazwy elementu i text()uzyskania wartości.

  select C1.PK,
         C1.UpdateDate,
         T.X.value('local-name(.)', 'nvarchar(128)') as ColumnName,
         T.X.value('text()[1]', 'varchar(max)') as Value
  from C1
    cross apply C1.X.nodes('row/*') as T(X)

Pełne rozwiązanie poniżej. Zauważ, że Versionjest odwrócony. 0 = ostatnia wersja.

create table #X
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  Version int not null,
  RowData xml not null
);

create table #T
(
  PK varchar(12) not null,
  UpdateDate datetime not null,
  ColumnName nvarchar(128) not null,
  Value varchar(max),
  Version int not null
);


insert into #X(PK, UpdateDate, Version, RowData)
select bt.PK,
       bt.UpdateDate,
       0,
       (select bt.* for xml path(''), elements xsinil, type)
from dbo.bigtable as bt
union all
select bt.PK,
       bt.UpdateDate,
       row_number() over(partition by bt.PK order by bt.UpdateDate desc),
       (select bt.* for xml path(''), elements xsinil, type)
from dbo.bigtable_archive as bt;

with C as 
(
  select X.PK,
         X.UpdateDate,
         X.Version,
         T.C.value('local-name(.)', 'nvarchar(128)') as ColumnName,
         T.C.value('text()[1]', 'varchar(max)') as Value
  from #X as X
    cross apply X.RowData.nodes('*') as T(C)
)
insert into #T (PK, UpdateDate, ColumnName, Value, Version)
select C.PK,
       C.UpdateDate,
       C.ColumnName,
       C.Value,
       C.Version
from C 
where C.ColumnName not in (N'PK', N'UpdateDate');

/*
option (querytraceon 8649);

The above query might need some trick to go parallel.
For the testdata I had on my machine exection time is 16 seconds vs 2 seconds
https://sqlkiwi.blogspot.com/2011/12/forcing-a-parallel-query-execution-plan.html
http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/

*/

select New.PK,
       New.UpdateDate,
       New.ColumnName,
       Old.Value as OldValue,
       New.Value as NewValue
from #T as New
  left outer join #T as Old
    on Old.PK = New.PK and
       Old.ColumnName = New.ColumnName and
       Old.Version = New.Version + 1;

Proponuję ci inne podejście.

Chociaż nie możesz zmienić bieżącej aplikacji, być może możesz zmienić zachowanie bazy danych.

Jeśli to możliwe, dodam dwa TRIGGERY do bieżących tabel.

Jeden INSTEAD OF INSERT na dbo.bigtable_archive, który dodaje nowy rekord tylko wtedy, gdy obecnie nie istnieje.

CREATE TRIGGER dbo.IoI_BTA
ON dbo.bigtable_archive
INSTEAD OF INSERT
AS
BEGIN
    IF NOT EXISTs(SELECT 1 
                  FROM dbo.bigtable_archive bta
                  INNER JOIN inserted i
                  ON  bta.PK = i.PK
                  AND bta.UpdateDate = i.UpdateDate)
    BEGIN
        INSERT INTO dbo.bigtable_archive
        SELECT * FROM inserted;
    END
END

I wyzwalacz PO WSTAWIENIU na bigtable, które wykonują dokładnie to samo zadanie, ale wykorzystują dane z bigtable.

CREATE TRIGGER dbo.IoI_BT
ON dbo.bigtable
AFTER INSERT
AS
BEGIN
    IF NOT EXISTS(SELECT 1 
                  FROM dbo.bigtable_archive bta
                  INNER JOIN inserted i
                  ON  bta.PK = i.PK
                  AND bta.UpdateDate = i.UpdateDate)
    BEGIN
        INSERT INTO dbo.bigtable_archive
        SELECT * FROM inserted;
    END
END

Ok, ustawiłem tutaj mały przykład z następującymi wartościami początkowymi:

SELECT * FROM bigtable;
SELECT * FROM bigtable_archive;

UpdateDate | PK | col1 | col2 | col3
: ------------------ | : - | : --- | ---: | : ---
02.01.2017 00:00:00 | ABC | C3 | 1 | C1  

UpdateDate | PK | col1 | col2 | col3
: ------------------ | : - | : --- | ---: | : ---
01.01.2017 00:00:00 | ABC | C1 | 1 | C1  

Teraz powinieneś wstawić do bigtable_archive wszystkie oczekujące rekordy z bigtable.

INSERT INTO bigtable_archive
SELECT *
FROM   bigtable
WHERE  UpdateDate >= '20170102';

SELECT * FROM bigtable_archive;
GO

UpdateDate | PK | col1 | col2 | col3
: ------------------ | : - | : --- | ---: | : ---
01.01.2017 00:00:00 | ABC | C1 | 1 | C1  
02.01.2017 00:00:00 | ABC | C3 | 1 | C1  

Teraz, gdy następnym razem aplikacja spróbuje wstawić rekord do tabeli bigtable_archive, wyzwalacze wykryją, czy istnieje, i można go wstawić.

INSERT INTO dbo.bigtable_archive VALUES('20170102', 'ABC', 'C3', 1, 'C1');
GO

SELECT * FROM bigtable_archive;
GO

UpdateDate | PK | col1 | col2 | col3
: ------------------ | : - | : --- | ---: | : ---
01.01.2017 00:00:00 | ABC | C1 | 1 | C1  
02.01.2017 00:00:00 | ABC | C3 | 1 | C1  

Oczywiście teraz możesz uzyskać oś czasu zmian, sprawdzając tylko tabelę archiwum. Aplikacja nigdy nie zda sobie sprawy, że wyzwalacz po cichu wykonuje pracę pod przykryciem.

dbfiddle tutaj

Roboczą propozycję, z pewnymi przykładowymi danymi, można znaleźć @ rextester: bigtable unpivot

Istota operacji:

1 - Użyj syscolumns i xml do dynamicznego generowania naszych list kolumn dla operacji unpivot; wszystkie wartości zostaną przekonwertowane na varchar (maks.), w / NULL zostaną przekonwertowane na ciąg „NULL” (rozwiązuje to problem z pomijaniem wartości NULL przez unpivot)

2 - Wygeneruj zapytanie dynamiczne, aby rozdzielić dane w tabeli tymczasowej #columns

• Dlaczego tabela temp vs CTE (przez z klauzulą)? zaniepokojony potencjalnym problemem związanym z wydajnością dużej ilości danych i samozłączeniem CTE bez użytecznego indeksu / schematu mieszania; tabela temp pozwala na utworzenie indeksu, który powinien poprawić wydajność samozłączenia [patrz powolne samodzielne łączenie CTE ]
• Dane są zapisywane do # kolumn w PK + ColName + UpdateDate, co pozwala nam przechowywać wartości PK / Colname w sąsiednich wierszach; kolumna tożsamości ( rid ) pozwala nam łączyć się z tymi kolejnymi wierszami poprzez rid = rid + 1

3 - Wykonaj samodzielne połączenie w tabeli #temp, aby wygenerować pożądany wynik

Wycinanie i wklejanie z rextestera ...

Utwórz przykładowe dane i naszą tabelę #columns:

CREATE TABLE dbo.bigtable
(UpdateDate datetime      not null
,PK         varchar(12)   not null
,col1       varchar(100)      null
,col2       int               null
,col3       varchar(20)       null
,col4       datetime          null
,col5       char(20)          null
,PRIMARY KEY (PK)
);

CREATE TABLE dbo.bigtable_archive
(UpdateDate datetime      not null
,PK         varchar(12)   not null
,col1       varchar(100)      null
,col2       int               null
,col3       varchar(20)       null
,col4       datetime          null
,col5       char(20)          null
,PRIMARY KEY (PK, UpdateDate)
);

insert into dbo.bigtable         values ('20170512', 'ABC', NULL, 6, 'C1', '20161223', 'closed')

insert into dbo.bigtable_archive values ('20170427', 'ABC', NULL, 6, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170315', 'ABC', NULL, 5, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170212', 'ABC', 'C1', 1, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170109', 'ABC', 'C1', 1, 'C1', '20160513', 'open')

insert into dbo.bigtable         values ('20170526', 'XYZ', 'sue', 23, 'C1', '20161223', 're-open')

insert into dbo.bigtable_archive values ('20170401', 'XYZ', 'max', 12, 'C1', '20160825', 'cancel')
insert into dbo.bigtable_archive values ('20170307', 'XYZ', 'bob', 12, 'C1', '20160825', 'cancel')
insert into dbo.bigtable_archive values ('20170223', 'XYZ', 'bob', 12, 'C1', '20160820', 'open')
insert into dbo.bigtable_archive values ('20170214', 'XYZ', 'bob', 12, 'C1', '20160513', 'open')
;

create table #columns
(rid        int           identity(1,1)
,PK         varchar(12)   not null
,UpdateDate datetime      not null
,ColName    varchar(128)  not null
,ColValue   varchar(max)      null
,PRIMARY KEY (rid, PK, UpdateDate, ColName)
);

Odwaga rozwiązania:

declare @columns_max varchar(max),
        @columns_raw varchar(max),
        @cmd         varchar(max)

select  @columns_max = stuff((select ',isnull(convert(varchar(max),'+name+'),''NULL'') as '+name
                from    syscolumns
                where   id   = object_id('dbo.bigtable')
                and     name not in ('PK','UpdateDate')
                order by name
                for xml path(''))
            ,1,1,''),
        @columns_raw = stuff((select ','+name
                from    syscolumns
                where   id   = object_id('dbo.bigtable')
                and     name not in ('PK','UpdateDate')
                order by name
                for xml path(''))
            ,1,1,'')


select @cmd = '
insert #columns (PK, UpdateDate, ColName, ColValue)
select PK,UpdateDate,ColName,ColValue
from
(select PK,UpdateDate,'+@columns_max+' from bigtable
 union all
 select PK,UpdateDate,'+@columns_max+' from bigtable_archive
) p
unpivot
  (ColValue for ColName in ('+@columns_raw+')
) as unpvt
order by PK, ColName, UpdateDate'

--select @cmd

execute(@cmd)

--select * from #columns order by rid
;

select  c2.PK, c2.UpdateDate, c2.ColName as ColumnName, c1.ColValue as 'Old Value', c2.ColValue as 'New Value'
from    #columns c1,
        #columns c2
where   c2.rid                       = c1.rid + 1
and     c2.PK                        = c1.PK
and     c2.ColName                   = c1.ColName
and     isnull(c2.ColValue,'xxx')   != isnull(c1.ColValue,'xxx')
order by c2.UpdateDate, c2.PK, c2.ColName
;

A wyniki:

Uwaga: przepraszam ... nie mogłem znaleźć prostego sposobu na wycięcie i wklejenie wyniku rextestera do bloku kodu. Jestem otwarty na sugestie.

Potencjalne problemy / obawy:

1 - konwersja danych na ogólny varchar (max) może prowadzić do utraty dokładności danych, co z kolei może oznaczać, że przegapimy pewne zmiany danych; rozważ następujące pary dat i liczb zmiennoprzecinkowych, które po przekonwertowaniu / rzutowaniu na ogólny „varchar (max)” tracą swoją precyzję (tzn. przekonwertowane wartości są takie same):

original value       varchar(max)
-------------------  -------------------
06/10/2017 10:27:15  Jun 10 2017 10:27AM
06/10/2017 10:27:18  Jun 10 2017 10:27AM

    234.23844444                 234.238
    234.23855555                 234.238

    29333488.888            2.93335e+007
    29333499.999            2.93335e+007

Chociaż można by zachować precyzję danych, wymagałoby to nieco więcej kodowania (np. Rzutowania w oparciu o typy danych w kolumnie źródłowej); na razie zdecydowałem się trzymać ogólnego varchara (maks.) zgodnie z zaleceniem PO (i założenie, że OP zna dane wystarczająco dobrze, aby wiedzieć, że nie napotkamy żadnych problemów z utratą precyzji danych).

2 - w przypadku naprawdę dużych zestawów danych istnieje ryzyko wysadzenia niektórych zasobów serwera, bez względu na to, czy jest to przestrzeń tempdb i / lub pamięć podręczna / pamięć; główny problem pochodzi z eksplozji danych, która ma miejsce podczas rozpadu (np. przechodzimy z 1 wiersza i 302 kawałków danych do 300 wierszy i 1200-1500 kawałków danych, w tym 300 kopii kolumn PK i UpdateDate, 300 nazw kolumn)

To podejście wykorzystuje dynamiczne zapytanie do wygenerowania kodu SQL w celu uzyskania zmian. SP przyjmuje nazwę tabeli i schematu i daje pożądane wyniki.

Zakłada się, że kolumny PK i UpdateDate są obecne we wszystkich tabelach. Wszystkie tabele archiwów mają format originalTableName + „_archive” ..

NB: Nie sprawdziłem go pod kątem wydajności.

Uwaga: ponieważ używa to dynamicznego sql, powinienem dodać zastrzeżenie dotyczące bezpieczeństwa / iniekcji sql. Ogranicz dostęp do SP i dodaj inne walidacje, aby zapobiec wstrzykiwaniu SQL.

    CREATE proc getTableChanges
    @schemaname  varchar(255),
    @tableName varchar(255)
    as

    declare @strg nvarchar(max), @colNameStrg nvarchar(max)='', @oldValueString nvarchar(max)='', @newValueString nvarchar(max)=''

    set @strg = '
    with cte as (

    SELECT  * , ROW_NUMBER() OVER(partition by PK ORDER BY UpdateDate) as RowNbr
    FROM    (

        SELECT  *
        FROM    [' + @schemaname + '].[' + @tableName + ']

        UNION

        SELECT  *
        FROM    [' + @schemaname + '].[' + @tableName + '_archive]

        ) a

    )
    '


    SET @strg = @strg + '

    SELECT  a.pk, a.updateDate, 
    CASE '

    DECLARE @colName varchar(255)
    DECLARE cur CURSOR FOR
        SELECT  COLUMN_NAME
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @schemaname
        AND TABLE_NAME = @tableName
        AND COLUMN_NAME NOT IN ('PK', 'Updatedate')

    OPEN cur
    FETCH NEXT FROM cur INTO @colName 

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @colNameStrg  = @colNameStrg  + ' when a.' + @colName + ' <> b.' + @colName + ' then ''' + @colName + ''' '
        SET @oldValueString = @oldValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(a.' + @colName + ' as varchar(max))'
        SET @newValueString = @newValueString + ' when a.' + @colName + ' <> b.' + @colName + ' then cast(b.' + @colName + ' as varchar(max))'


    FETCH NEXT FROM cur INTO @colName 
    END

    CLOSE cur
    DEALLOCATE cur


    SET @colNameStrg = @colNameStrg  + '    END as ColumnChanges '
    SET @oldValueString = 'CASE ' + @oldValueString + ' END as OldValue'
    SET @newValueString = 'CASE ' + @newValueString + ' END as NewValue'

    SET @strg = @strg + @colNameStrg + ',' + @oldValueString + ',' + @newValueString

    SET @strg = @strg + '
        FROM    cte a join cte b on a.PK = b.PK and a.RowNbr + 1 = b.RowNbr 
        ORDER BY  a.pk, a.UpdateDate
    '

    print @strg

    execute sp_executesql @strg


    go

Przykładowe połączenie:

exec getTableChanges 'dbo', 'bigTable'

Używam AdventureWorks2012`, Production.ProductCostHistory i Production.ProductListPriceHistory w moim przykładzie. Może to nie być doskonały przykład tabeli historii, „ale skrypt jest w stanie zebrać pożądane dane wyjściowe i prawidłowe dane wyjściowe”.

     DECLARE @sql NVARCHAR(MAX)
    ,@columns NVARCHAR(Max)
    ,@table VARCHAR(200) = 'ProductCostHistory'
    ,@Schema VARCHAR(200) = 'Production'
    ,@Archivecolumns NVARCHAR(Max)
    ,@ColForUnpivot NVARCHAR(Max)
    ,@ArchiveColForUnpivot NVARCHAR(Max)
    ,@PKCol VARCHAR(200) = 'ProductID'
    ,@UpdatedCol VARCHAR(200) = 'modifiedDate'
    ,@Histtable VARCHAR(200) = 'ProductListPriceHistory'
SELECT @columns = STUFF((
            SELECT ',CAST(p.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME(column_name)
            FROM information_schema.columns
            WHERE table_name = @table
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@Archivecolumns = STUFF((
            SELECT ',CAST(p1.' + QUOTENAME(column_name) + ' AS VARCHAR(MAX)) AS ' + QUOTENAME('A_' + column_name)
            FROM information_schema.columns
            WHERE table_name = @Histtable
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@ColForUnpivot = STUFF((
            SELECT ',' + QUOTENAME(column_name)
            FROM information_schema.columns
            WHERE table_name = @table
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')
    ,@ArchiveColForUnpivot = STUFF((
            SELECT ',' + QUOTENAME('A_' + column_name)
            FROM information_schema.columns
            WHERE table_name = @Histtable
                AND column_name NOT IN (
                    @PKCol
                    ,@UpdatedCol
                    )
            ORDER BY ORDINAL_POSITION
            FOR XML PATH('')
            ), 1, 1, '')

--SELECT @columns   ,@Archivecolumns    ,@ColForUnpivot
SET @sql = N' 
    SELECT ' + @PKCol + ', ColumnName,
            OldValue,NewValue,' + @UpdatedCol + '
    FROM    (  
    SELECT p.' + @PKCol + '
        ,p.' + @UpdatedCol + '
        ,' + @columns + '
        ,' + @Archivecolumns + '
    FROM ' + @Schema + '.' + @table + ' p
    left JOIN ' + @Schema + '.' + @Histtable + ' p1 ON p.' + @PKCol + ' = p1.' + @PKCol + '

  ) t
    UNPIVOT (
        OldValue
        FOR ColumnName in (' + @ColForUnpivot + ')
    ) up

     UNPIVOT (
        NewValue
        FOR ColumnName1 in (' + @ArchiveColForUnpivot + ')
    ) up1

--print @sql
EXEC (@sql)

W wewnętrznym zapytaniu Select rozważ p jako tabelę główną, a p1 jako tabelę historii. W unpivot ważne jest przekonwertowanie go na ten sam typ.

Możesz wziąć dowolną inną nazwę tabeli o mniejszej nazwie kolumny, aby zrozumieć mój skrypt. Każde wyjaśnienie musi mnie pingować.