{"id":159,"date":"2006-10-14T19:46:10","date_gmt":"2006-10-14T17:46:10","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/2006\/08\/13\/sql-talk\/sql-server-2005-pivoting-unpivoting"},"modified":"2007-10-19T09:47:24","modified_gmt":"2007-10-19T07:47:24","slug":"sql-server-2005-pivoting-unpivoting","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2006\/10\/14\/sql-talk\/sql-server-2005-pivoting-unpivoting","title":{"rendered":"SQL Server 2005: Pivoting & Unpivoting"},"content":{"rendered":"

Dem Feature "Pivoting\/Unpivoting" stehe ich etwas zwispältig gegenüber: Einerseits habe ich in der Vergangenheit schon mehrfach die Anfrage von Entwicklern bekommen, wie man sowas macht (meist noch in den späten 90ern als wir von Btrieve auf Sybase SQL-Anywhere umstellten). Andererseits sind die zugrundeliegenden Ursachen in der Regel eine "schlechte" Datenmodelierung: Wert-Tabellen, wie sie ein index-sequentiellen Systemen üblich waren. Ich bevorzuge echte relationale Datenmodelle. <\/p>\n

In den letzten Jahren habe ich aber auch erlebt, dass es mindestens eine Situation gibt, bei der man um solche Tabellen nicht rumkommt: Wenn man mit relationalen Mitteln eine Art OLAP-System nachbilden will, dann ist es sinnvoll alle möglichen "Fakten" in der gleichen Tabelle unterzubringen und deren Werte im gleichen Feld zu speichern. Damit kann man dann sehr einfach, sehr flexible Auswertungsmöglichkeiten schaffen. Mein Kollege Michael brauchte ziemlich lange, um mich davon zu überzeugen… \ud83d\ude09<\/p>\n

\"Pivoting\"
\nDie Werte aus einer Werte-Tabelle werden in eine echte Tabellen übertragen.
\nDabei bekommen die einzelnen Attribute auch gleich sinnvolle Namen, z.B. wird "attr1" zu "Typ", "attr2" zu Datum" "attr3" zu "Anzahl" usw.<\/p>\n

Der entsprechende PIVOT-Befehl geht so:<\/p>\n

SELECT ObjectID, attr1 as Typ,
\n\tattr2 as Datum, attr3 as Anzahl,
\n\tattr4 as Dings, attr5 as Bums
\nFROM OpenSchema
\n\t\tPIVOT(\tMax("Value")
\n\t\t\tFOR Attribute IN ("attr1", "attr2", "attr3", "attr4", "attr5")
\n\t\t) AS pvt
\nORDER BY ObjectID<\/code><\/p>\n

Wenn man das Bedürfnis hat genau den umgekehrten Weg zu gehen: aus einer normalisierten Tabelle die Ergebnisse in Form einer Wert-Tabelle zu bekommen, dann geht das mit UNPIVOT:<\/p>\n

SELECT ObjectID, Attribute, \"Value\"
\nFROM (\tSELECT ObjectID, cast(Typ as sql_variant) as attr1, cast(Datum as sql_variant) as attr2, cast(Anzahl as sql_variant) as attr3, cast(Dings as sql_variant) as attr4, cast(Bums as sql_variant) as attr5
\n\t\t\tFROM FixSchema) as "Value"
\n\tUNPIVOT
\n\t(
\n\t\t"Value"
\n\t\tFOR Attribute IN ("attr1", "attr2", "attr3", "attr4", "attr5")
\n\t) AS pvt
\nORDER BY ObjectID<\/code><\/p>\n

Wer noch genug hat: Eine besonders gute Darstellung des Pivotierens liefert wieder mal Itzik Ben-Gan. Die Folien von seinem Vortrag "Advanced T-SQL Techniques" zur TechEd 2006 in Israel stehen bei microsoft.com<\/a> (siehe "Advanced T-SQL Techniques"). Ich glaube, sie sind auch ohne seinen Text verständlich. Als ich ihn 2005 in London persönlich erleben durfte, zeigte er ganz ähnliche Folien. Deswegen bin ich da nicht repräsentativ.<\/p>\n","protected":false},"excerpt":{"rendered":"

Dem Feature "Pivoting\/Unpivoting" stehe ich etwas zwispältig gegenüber: Einerseits habe ich in der Vergangenheit schon mehrfach die Anfrage von Entwicklern bekommen, wie man sowas macht (meist noch in den späten 90ern als wir von Btrieve auf Sybase SQL-Anywhere umstellten). Andererseits sind die zugrundeliegenden Ursachen in der Regel eine "schlechte" Datenmodelierung: Wert-Tabellen, wie sie ein index-sequentiellen […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/159"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=159"}],"version-history":[{"count":0,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/159\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=159"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=159"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=159"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}