Trochę się zmieniły tabele, z pytania źródłowego, ale nie mam za bardzo czasu na przygotowywanie tego w fiddle, więc zrzut jak wyglądają teraz:
Dodatkowa tabela, dla zestawu danych:
Pierwsza ma 1 598 042 rekordów druga: 105 349, więc nadal rzeźbimy w g... tylko bardzo dużym ;)
Więc przygotowujemy dane, aby doprowadzić je do takiej postaci:
Które zapisujemy do tabeli tymczasowej #tmp, a reszta to dynamiczne wygenerowanie zapytania do wyciągnięcia interesujących wartości:
Skrypt zwracający wynik:
with preData as (
Select
ach.itemid,
ValidFrom,
ISNULL(ach.ValidTo, '2099-12-31') ValidTo,
rtrim(cast(BusinessCase as varchar(30))) BusinessCase,
rtrim(cast(SelectionCode as varchar(30))) SelectionCode,
rtrim(cast(SignalCode as varchar(30))) SignalCode,
rtrim(cast(Width as varchar(30))) Width,
rtrim(cast(Unspsc as varchar(30))) Unspsc
FROM
[ArticleChangeHistory] ach
INNER JOIN finalSetToBeCheck plm ON ach.ItemID = plm.ITemID AND ISNULL(ach.ValidTo, '2099-12-31') > plm.DateContractPrice
), unp as (
select
*
from
preData
unpivot (
Val FOR Col IN (BusinessCase, SelectionCode, SignalCode, Width, Unspsc)
) t
), z as (
select
*
, CASE WHEN LAG(val,1) OVER (PARTITION BY itemID,col ORDER BY validTo) IS NULL OR LAG(val,1) OVER (PARTITION BY itemID,col ORDER BY validTo) <> val THEN
1
ELSE
0
END c
from unp)
select ROW_NUMBER() over(partition by itemID,col order by validTo) rn, * into #tmp from z where c=1
declare @i as integer
declare @cols nvarchar(max)
declare @change_col nvarchar(max)
declare @sql nvarchar(max)
select @i = max(rn) from #tmp
SET @cols = STUFF((select
' max(case when col = ''' + col + ''' and rn='+n+' then val else null end) as ['+col+n+'], '
from (select distinct
col
from
#tmp) t
inner join (select cast(number as varchar(6)) n,number from master..spt_values where type = 'p' and number between 1 and @i
) as l on 1=1 order by Col, number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @change_col = STUFF((select ' ' +
case when number = 1 then
'case when '+col+'1 <> isnull('+col+'2,'+col+'1) then '''+col+': '' + ' + col+n
else
''
end
+case when number > 1 then
'+isnull('', ''+' + col+n+','''')' else '' end
+ case when number = @i then '+''; '' else '''' end + ' else '' end
from (select distinct
col
from
#tmp) t
inner join (select cast(number as varchar(6)) n,number from master..spt_values where type = 'p' and number between 1 and @i
) as l on 1=1 order by Col, number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @change_col = LEFT(@change_col,len(@change_col)-2) + ' as Changes'
set @sql = N'select
itemid,
'+@change_col+'
from (select
'+@cols+ N'
itemid
from
#tmp
group by
itemid
) as dt'
execute (@sql)
drop table #tmp
Zapytanie generowane przez skrypt:
SELECT itemid,
CASE
WHEN businesscase1 <> Isnull(businesscase2, businesscase1)
THEN 'BusinessCase: '+businesscase1+Isnull(', '+businesscase2, '')+Isnull(', '+businesscase3, '')+Isnull(', '+businesscase4, '')+Isnull(', '+businesscase5, '')+Isnull(', '+businesscase6, '')+Isnull(', '+businesscase7, '')+Isnull(', '+businesscase8, '')+'; '
ELSE ''
END
+CASE
WHEN selectioncode1 <> Isnull(selectioncode2, selectioncode1)
THEN 'SelectionCode: '+selectioncode1+Isnull(', '+selectioncode2, '')+Isnull(', '+selectioncode3, '')+Isnull(', '+selectioncode4, '')+Isnull(', '+selectioncode5, '')+Isnull(', '+selectioncode6, '')+Isnull(', '+selectioncode7, '')+Isnull(', '+selectioncode8, '')+'; '
ELSE ''
END
+CASE
WHEN signalcode1 <> Isnull(signalcode2, signalcode1)
THEN 'SignalCode: '+signalcode1+Isnull(', '+signalcode2, '')+Isnull(', '+signalcode3, '')+Isnull(', '+signalcode4, '')+Isnull(', '+signalcode5, '')+Isnull(', '+signalcode6, '')+Isnull(', '+signalcode7, '')+Isnull(', '+signalcode8, '')+'; '
ELSE ''
END
+CASE
WHEN unspsc1 <> Isnull(unspsc2, unspsc1)
THEN 'Unspsc: '+unspsc1+Isnull(', '+unspsc2, '')+Isnull(', '+unspsc3, '')+Isnull(', '+unspsc4, '')+Isnull(', '+unspsc5, '')+Isnull(', '+unspsc6, '')+Isnull(', '+unspsc7, '')+Isnull(', '+unspsc8, '')+'; '
ELSE ''
END
+CASE
WHEN width1 <> Isnull(width2, width1)
THEN 'Width: '+width1+Isnull(', '+width2, '')+Isnull(', '+width3, '')+Isnull(', '+width4, '')+Isnull(', '+width5, '')+Isnull(', '+width6, '')+Isnull(', '+width7, '')+Isnull(', '+width8, '')+'; '
ELSE ''
END AS Changes
FROM
(
SELECT MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 1
THEN val
ELSE NULL
END) AS [BusinessCase1],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 2
THEN val
ELSE NULL
END) AS [BusinessCase2],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 3
THEN val
ELSE NULL
END) AS [BusinessCase3],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 4
THEN val
ELSE NULL
END) AS [BusinessCase4],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 5
THEN val
ELSE NULL
END) AS [BusinessCase5],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 6
THEN val
ELSE NULL
END) AS [BusinessCase6],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 7
THEN val
ELSE NULL
END) AS [BusinessCase7],
MAX(CASE
WHEN col = 'BusinessCase'
AND rn = 8
THEN val
ELSE NULL
END) AS [BusinessCase8],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 1
THEN val
ELSE NULL
END) AS [SelectionCode1],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 2
THEN val
ELSE NULL
END) AS [SelectionCode2],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 3
THEN val
ELSE NULL
END) AS [SelectionCode3],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 4
THEN val
ELSE NULL
END) AS [SelectionCode4],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 5
THEN val
ELSE NULL
END) AS [SelectionCode5],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 6
THEN val
ELSE NULL
END) AS [SelectionCode6],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 7
THEN val
ELSE NULL
END) AS [SelectionCode7],
MAX(CASE
WHEN col = 'SelectionCode'
AND rn = 8
THEN val
ELSE NULL
END) AS [SelectionCode8],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 1
THEN val
ELSE NULL
END) AS [SignalCode1],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 2
THEN val
ELSE NULL
END) AS [SignalCode2],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 3
THEN val
ELSE NULL
END) AS [SignalCode3],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 4
THEN val
ELSE NULL
END) AS [SignalCode4],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 5
THEN val
ELSE NULL
END) AS [SignalCode5],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 6
THEN val
ELSE NULL
END) AS [SignalCode6],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 7
THEN val
ELSE NULL
END) AS [SignalCode7],
MAX(CASE
WHEN col = 'SignalCode'
AND rn = 8
THEN val
ELSE NULL
END) AS [SignalCode8],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 1
THEN val
ELSE NULL
END) AS [Unspsc1],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 2
THEN val
ELSE NULL
END) AS [Unspsc2],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 3
THEN val
ELSE NULL
END) AS [Unspsc3],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 4
THEN val
ELSE NULL
END) AS [Unspsc4],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 5
THEN val
ELSE NULL
END) AS [Unspsc5],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 6
THEN val
ELSE NULL
END) AS [Unspsc6],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 7
THEN val
ELSE NULL
END) AS [Unspsc7],
MAX(CASE
WHEN col = 'Unspsc'
AND rn = 8
THEN val
ELSE NULL
END) AS [Unspsc8],
MAX(CASE
WHEN col = 'Width'
AND rn = 1
THEN val
ELSE NULL
END) AS [Width1],
MAX(CASE
WHEN col = 'Width'
AND rn = 2
THEN val
ELSE NULL
END) AS [Width2],
MAX(CASE
WHEN col = 'Width'
AND rn = 3
THEN val
ELSE NULL
END) AS [Width3],
MAX(CASE
WHEN col = 'Width'
AND rn = 4
THEN val
ELSE NULL
END) AS [Width4],
MAX(CASE
WHEN col = 'Width'
AND rn = 5
THEN val
ELSE NULL
END) AS [Width5],
MAX(CASE
WHEN col = 'Width'
AND rn = 6
THEN val
ELSE NULL
END) AS [Width6],
MAX(CASE
WHEN col = 'Width'
AND rn = 7
THEN val
ELSE NULL
END) AS [Width7],
MAX(CASE
WHEN col = 'Width'
AND rn = 8
THEN val
ELSE NULL
END) AS [Width8],
itemid
FROM #tmp
GROUP BY itemid
) AS dt;
Na moim serwerze trwa to 30 sekund...