{"id":76,"date":"2010-07-19T15:53:41","date_gmt":"2010-07-19T13:53:41","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=76"},"modified":"2010-07-19T16:12:41","modified_gmt":"2010-07-19T14:12:41","slug":"ms-sql-server-tabelle-mit-pivot-drehen-z-b-fur-zeitreihenanalyse","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=76","title":{"rendered":"MS SQL Server-Tabelle mit Pivot drehen (z.B. f\u00fcr Zeitreihenanalyse)"},"content":{"rendered":"

Problemfeldbeschreibung<\/h1>\n

Beim Arbeiten mit Daten im Microsoft SQL Server kommt man gelegentlich an den Punkt, an dem man sich das „Drehen“ von Tabellendaten als n\u00fctzlich entpuppt. Anwendungsgebiete sind u.a. die Prognoserechnung, Zeitreihenanalyse oder einfach Matrizenmultiplikation. <\/p>\n

Nehmen wir z.B. den folgenden Datensatz, er soll gedreht werden:<\/p>\n

\r\n200901\u00a0\u00a0 \u00a0\u00a01\u00a0\u00a0 \u00a02009\u00a0\u00a0 \u00a00\u00a0\u00a0 \u00a030\r\n200902\u00a0\u00a0 \u00a0\u00a02\u00a0\u00a0 \u00a02009\u00a0\u00a0 \u00a058\u00a0\u00a0 \u00a016\r\n200903\u00a0\u00a0 \u00a0\u00a03\u00a0\u00a0 \u00a02009\u00a0\u00a0 \u00a0520\u00a0\u00a0 \u00a015\r\n200906\u00a0\u00a0 \u00a0\u00a06\u00a0\u00a0 \u00a02009\u00a0\u00a0 \u00a0780\u00a0\u00a0 \u00a015\r\n200809\u00a0\u00a0 \u00a0\u00a09\u00a0\u00a0 \u00a02008\u00a0\u00a0 \u00a00\u00a0\u00a0 \u00a07\r\n200909\u00a0\u00a0 \u00a0 9\u00a0\u00a0 \u00a02009\u00a0\u00a0 \u00a0260\u00a0\u00a0 \u00a08\r\n200810\u00a0\u00a0 \u00a010\u00a0\u00a0 \u00a02008\u00a0\u00a0 \u00a00\u00a0\u00a0 \u00a0\r\n<\/pre>\n
Der unerfahrene Datenbankentwickler beginnt dann h\u00e4ufig mit der Implementierung von langsamen Cursorskripten \u00e1 la<\/p>\n
DECLARE @compcode  varchar(255)
\nDECLARE @market  varchar(255)
\nDECLARE @salesdiv  varchar(255)
\nDECLARE @material  varchar(255)
\nDECLARE @month  int
\nDECLARE @year  int
\nDECLARE @bruttoumsatz  varchar(255)
\nDECLARE @amount varchar(255)
\nDECLARE @spalte varchar(255)<\/p>\n
DECLARE mmg_cursor CURSOR FOR
\nSELECT compcode, market, salesdiv, material, month, year, bruttoumsatz, amount
\nFROM cube WHERE CAST(CAST(year as varchar)+(CASE WHEN month<10 THEN \n         ('0'+CAST(month as varchar)) \n        ELSE CAST(@month as varchar) END ) as int) >=200610<\/p>\n
OPEN mmg_cursor<\/p>\n
FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material,
\n                                                  @month, @year, @bruttoumsatz, @amount<\/p>\n
WHILE @@FETCH_STATUS = 0
\nBEGIN<\/p>\n
\tSET @spalte=CAST(@year as varchar)+(CASE WHEN @month<10 THEN\n               ('0'+CAST(@month as varchar)) \n               ELSE CAST(@month as varchar) END )\n\n\tEXEC('UPDATE hypercube SET ['+@spalte+']='+@amount+' \n                WHERE [compcode]='''+@compcode+''' AND [salesdiv]='''+@salesdiv+''' \n                AND [market]='''+@market+''' AND [material]='''+@material+''' ')\n\n    FETCH NEXT FROM mmg_cursor INTO @compcode,@market, @salesdiv, @material, \n                                       @month, @year, @bruttoumsatz, @amount\nEND\n\nDEALLOCATE mmg_cursor\n[\/javascript]\n\n\n\n
Ansatz<\/h1>\n
Es bedarf keines Benchmarks oder einer Messung um festzustellen, dass diese Methode sehr zeitaufw\u00e4ndig und unzeitgem\u00e4\u00df ist. Microsoft r\u00e4t von solchen Cursor-Skripten ab und r\u00e4t von der Nutzung der PIVOT-Funktion, die an Performance und Handhabung kaum zu \u00fcberbieten ist.<\/p>\n
L\u00f6sung<\/h1>\n
Das folgende SQL-Skript dreht die Tabelle mit PIVOT (Die MS SQL Server-Datenbank muss hierzu mind. auf MSSQL2005-Kompatibilit\u00e4t gestellt werden.<\/p>\n
declare @columns varchar(max)
\ndeclare @columnsoben varchar(max)
\ndeclare @count int
\ndeclare @max int
\ndeclare @current int
\ndeclare @sql nvarchar(max)
\ndeclare @t table(id int identity(1,1), datekey int)
\ninsert into @t select distinct datekey from vglobe2 order by 1
\nset @columns = “
\nset @columnsoben = “
\nset @count = 1
\nset @max = (select max(id) from @t)
\nwhile (@count <= @max)\nbegin\n\tset @current = (select datekey from @t where id = @count)\n\tset @columns = @columns +  '[' +  cast(@current as varchar(6)) +'], '\n\tset @columnsOben = @columnsOben +  '[' +  cast(@current as varchar(6)) +'],' +\n                                       '0 as [' +  cast(@current as varchar(6)) +'stat],'\n\tset @count = @count+1\nend\nset @columns = substring(@columns, 1, len(@columns)-1) -- letztes komma\nset @columnsOben = substring(@columnsOben, 1, len(@columnsOben)-1) -- letztes komma\nset @sql = 'SELECT Artikel, ' + @columnsoben +\n\t\t   N' FROM\n\t\t\t (SELECT artikel, bruttoumsatz , datekey FROM vglobe2) p\n\t\t\tPIVOT\n\t\t\t(\n\t\t\tsum(bruttoumsatz)\n\t\t\tFOR  datekey  in (' + @columns + N')\n\t\t\t\t) AS pvt\n\t\t\tORDER BY 1'\n\nprint @sql\nexec sp_executesql @sql\n\n[\/sql]\n<\/p>\n