T-SQL Pivot für Entity–Attribute–Value (EAV) Tabellen

Ein Kunde hat mir vor ein paar Tagen seinen Kummer mitgeteilt, wie Anwendungsentwickler in einem Projekt mit sogenannten Named-Value bzw .Entity-Attribute-Value Tabellen und deren Abfrage umgegangen sind.

Bei einem EAV Modell werden die Werte nicht in expliziten Spalten persistiert, sondern über einen Attributschlüssel verwaltet. Mehr siehe im Link oben (interessant in diesem Link auch der Hinweis auf Sparse columns, wobei ich diese nicht als Alternative sehe!).

Gegen die Verwendung von EAV Modellen spricht eigentlich nichts. Ich selbst habe diese Form der Modellierung in einigen Projekten vorgeschlagen, wenn die Herausforderung danach verlangte. Man erkauft sich eine Menge Flexibilität im Datenmodell mit der Last, dass man dieses später wesentlich aufwendiger in der Pflege und Abfrage ist. Nun ja, irgendwas ist ja immer.

Sowohl in OLTP als auch in Data Warehouse Projekten trifft man also gern auf so etwas. Im Data Warehouse Umfeld finden wir diese Form der Modellierung für Fakten- als auch für Dimensionstabellen ab und wann im sogenannten Core Data Warehouse, um eine größere Flexibilität bei der Ausgestaltung der Geschäftsobjekte zu erreichen. Auch SCD2 Herausforderungen lassen sich so zum Teil eleganter lösen. Immer eine Frage der Anforderungen usw.

Wie bereits erwähnt, ist neben der Pflege (ein Fluch) die effektive Abfrage von EAV Tabellen eine Herausforderung. Was ich aber nicht für möglich gehalten hätte, sind die Abfrage Monstren, die beim Kunden entstanden sind, welche ich hier selbst als Anti-Pattern nicht verewigen möchte.

Daher hier kurz die Grundlagen, Links und ein Buchtipp zum selber lesen und weiterreichen, wie man mit solchen Tabellen umgehen kann.

Unsere Beispieltabelle ist recht schlicht aufgebaut. Wir haben drei Geschäftsobjekte (Entitäten), welche jeweils drei Attribute mit entsprechenden Werten haben:

Ideal wäre es nun, wenn wir in einem Select einfach auf A1, A2 und A3 verweisen könnten. Die gute Nachricht ist, dass es dafür in T-SQL den PIVOT Operator gibt. Dieser ist nicht ganz intuitiv zu verwenden und häufig höre ich von Entwicklern, dass sie nicht die erhofften Ergebnisse bekommen, doch dazu gleich mehr.

Hier unsere Abfrage:

Das ist das Ergebnis:Perfekt! Alles gesagt, oder?

Häufig wird gesagt, dass der Pivot Operator nicht intuitiv ist. Stimmt! Woher weiß er zum Beispiel, dass in entityid unsere Entität steckt? ;-)

Ganz einfach: Weil sie im Statement nicht vorkommt! Verwirrend? Ja!

Und wozu die Aggregationsfunktion? In diesem Beispiel das MAX()? Dazu müssen wir uns mal die Frage stellen, was da im Hintergrund eigentlich passiert. Nämlich nichts anderes als ein Gruppieren. Und wonach gruppieren wir? Nach der Entität. 

Und die Aggregationsfunktion? Nun ja, der PIVOT Operator ist nicht nur für EAV Tabellen da, sondern auch, um wesentlich komplexere Aufgaben zu lösen. Dazu unten noch mehr. Zurück zum Gruppieren. Also es wird gruppiert, nach der Spalte, welche NICHT im Operator genutzt wird. Okay.

Nur was passiert, wenn unsere Tabelle eine weitere Spalte bekommt? Eigentlich stört so etwas eine SQL Abfrage ja nicht. Erweitern wir unsere Tabelle um einen Wert, welcher für uns erst mal völlig ohne Bedeutung sein sollte:

Wieder nutzen wir die Query von oben und bekommen dieses Resultat:

Autsch, was ist denn hier passiert?

Es wurde wieder gruppiert und zwar nach den SPALTEN, welche NICHT im PIVOT Operator vorkamen. Also nach: entityid und NewColumn. Das kann ja keiner wollen! Das heißt, dass durch die Erweiterung der Tabelle das Ergebnis geändert wurde. Um diesen Effekt zu verhindern, wird empfohlen die Source Tabelle nicht direkt anzusprechen, sondern über einen View, ein Subselect oder eine CTE.

Hier also eine modifizierte Version der Query von oben:

Und wir bekommen wieder das ursprüngliche Ergebnis zurück.

Der PIVOT Operator ist also ein wenig unhandlich im Alltag. Nun ist man aber nicht auf diesen angewiesen, um diese Funktionalität anzuwenden. Hier noch eine Alternative, welche durchaus ihren Charme hat aus dem Buch: Inside Microsoft SQL Server 2008: T-SQL Querying: T-SQL Querying:

Ich gehe auf den Mechanik der Logik im Hintergrund mal nicht tiefer ein und verweise auf das Buch. Nur ein Hinweis: Diese Variante ist immun gegenüber Veränderungen an der Source Tabelle! Und meine Erfahrung aus Coachings ist, dass viele Entwickler sich diese leichter “merken” können.

Noch mal zurück zum PIVOT Operator und der Aggregationsfunktion. Wie schon angedeutet, ist der Operator recht mächtig und kann für durchaus komplexere Abfragen genutzt werden.

Hier ein Beispiel direkt aus dem AdventureWorks Universum den BOL entnommen. http://technet.microsoft.com/de-de/library/ms177410(v=sql.105).aspx

Wir sehen auch gleich die Verwendung der Subquery im FROM. Als Aggregationsfunktion wird ein COUNT statt einem MAX verwendet. Wir sind die Belegköpfe, Mitarbeiter und Lieferanten mit jeweils ihrer Nummer enthalten.

Unsere Query gruppiert nun nach der VendorID und zählt für explizit in den Spalten definierte Mitarbeiter durch, wie häufig wer bei welchem Lieferanten bestellt hat. Hier das Ergebnis der Query:

Das ist durchaus ein valides Beispiel für eine etwas komplexere Anforderung, welche mit dem PIVOT Operator recht einfach umgesetzt werden konnte.