Problemfeldbeschreibung
Beim Arbeiten mit Daten im Microsoft SQL Server kommt man gelegentlich an den Punkt, an dem man sich das „Drehen“ von Tabellendaten als nützlich entpuppt. Anwendungsgebiete sind u.a. die Prognoserechnung, Zeitreihenanalyse oder einfach Matrizenmultiplikation.
Nehmen wir z.B. den folgenden Datensatz, er soll gedreht werden:
200901 1 2009 0 30 200902 2 2009 58 16 200903 3 2009 520 15 200906 6 2009 780 15 200809 9 2008 0 7 200909 9 2009 260 8 200810 10 2008 0
Der unerfahrene Datenbankentwickler beginnt dann häufig mit der Implementierung von langsamen Cursorskripten á la
DECLARE @compcode varchar(255)
DECLARE @market varchar(255)
DECLARE @salesdiv varchar(255)
DECLARE @material varchar(255)
DECLARE @month int
DECLARE @year int
DECLARE @bruttoumsatz varchar(255)
DECLARE @amount varchar(255)
DECLARE @spalte varchar(255)
DECLARE mmg_cursor CURSOR FOR
SELECT compcode, market, salesdiv, material, month, year, bruttoumsatz, amount
FROM cube WHERE CAST(CAST(year as varchar)+(CASE WHEN month<10 THEN
('0'+CAST(month as varchar))
ELSE CAST(@month as varchar) END ) as int) >=200610
OPEN mmg_cursor
FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material,
@month, @year, @bruttoumsatz, @amount
WHILE @@FETCH_STATUS = 0
BEGIN
SET @spalte=CAST(@year as varchar)+(CASE WHEN @month<10 THEN ('0'+CAST(@month as varchar)) ELSE CAST(@month as varchar) END ) EXEC('UPDATE hypercube SET ['+@spalte+']='+@amount+' WHERE [compcode]='''+@compcode+''' AND [salesdiv]='''+@salesdiv+''' AND [market]='''+@market+''' AND [material]='''+@material+''' ') FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material, @month, @year, @bruttoumsatz, @amount END DEALLOCATE mmg_cursor [/javascript]
Ansatz
Es bedarf keines Benchmarks oder einer Messung um festzustellen, dass diese Methode sehr zeitaufwändig und unzeitgemäß ist. Microsoft rät von solchen Cursor-Skripten ab und rät von der Nutzung der PIVOT-Funktion, die an Performance und Handhabung kaum zu überbieten ist.
Lösung
Das folgende SQL-Skript dreht die Tabelle mit PIVOT (Die MS SQL Server-Datenbank muss hierzu mind. auf MSSQL2005-Kompatibilität gestellt werden.
declare @columns varchar(max)
declare @columnsoben varchar(max)
declare @count int
declare @max int
declare @current int
declare @sql nvarchar(max)
declare @t table(id int identity(1,1), datekey int)
insert into @t select distinct datekey from vglobe2 order by 1
set @columns = “
set @columnsoben = “
set @count = 1
set @max = (select max(id) from @t)
while (@count <= @max)
begin
set @current = (select datekey from @t where id = @count)
set @columns = @columns + '[' + cast(@current as varchar(6)) +'], '
set @columnsOben = @columnsOben + '[' + cast(@current as varchar(6)) +'],' +
'0 as [' + cast(@current as varchar(6)) +'stat],'
set @count = @count+1
end
set @columns = substring(@columns, 1, len(@columns)-1) -- letztes komma
set @columnsOben = substring(@columnsOben, 1, len(@columnsOben)-1) -- letztes komma
set @sql = 'SELECT Artikel, ' + @columnsoben +
N' FROM
(SELECT artikel, bruttoumsatz , datekey FROM vglobe2) p
PIVOT
(
sum(bruttoumsatz)
FOR datekey in (' + @columns + N')
) AS pvt
ORDER BY 1'
print @sql
exec sp_executesql @sql
[/sql]
Cooles Blog, hab es gerade erst gefunden. Bei diesem Beitrag ist mir das Cursorskript aufgefallen. So eins habe ich auch schon mal programmiert und bin dann wahrscheinlich auch ein „unerfahrener Datenbankentwickler“… ;o)
Grüße aus HEF!