Pivot-Tabellen

engl. pivot [pɪvət], im Deutschen häufig französisch [pi.vo] ausgesprochen

Die Bezeichnung Pivot wurde dafür zuerst 1991 in DataPivot von Brio Technology, San Francisco verwendet.

Bei einer Pivot-Tabelle werden die Werte aus zwei Spalten zu Titeln von Spalten und Zeilen, an den Kreuzungen finden sich dann als Kreuzwerte die dazugehörigen Werte eines Wertes oder einer dritten Spalte (gegebenenfalls gefaltet, falls es mehrere dazugehörige Zeilen im Original gibt). Ergänzt werden kann das Ergebnis dann durch Faltungen seiner Zeilen und Spalten.

Kreuztabellen und Pivot-Tabellen sind in der Regel nicht zur maschinellen Weiterverarbeitung, sondern zu Sichtung durch einen Benutzer gedacht. Die Informationen über die Werte der einzelnen Zellen einer Kreuztabelle lassen sich noch gut durch jeweils eine SQL-Abfrage ermitteln. Schwierig ist es nur, sie zur vollständigen Kreuztabelle zusammenzusetzen. Dies kann man daher auch als ein Problem der Ausgabeformatierung ansehen. Die Ausgabeformatierung wird aber ohnehin eher als Aufgabe prozeduraler Klienten angesehen.

Mit deklarativem SQL  kann man statistische Auswertungen erledigen, bei denen Kennwerte einer Spalte ermittelt werden und mehrere Zeilen zusammengefaßt werden. Das Ermitteln von Kennzahlen, die sich nicht durch Zusammenfassungsoperationen (wie Addieren) darstellen lassen oder das Erstellen einer Tabelle, deren Spaltennamen aus Werten anderen Tabellen gewonnen werden ist schwierig.

Mit dem Mitteln des deklarativen SQL  von MySQL  gibt es keine Möglichkeit, um Kreuztabellen auf einfache Weise automatisch zu erstellen. Dies kann mit prozeduralen Mitteln erledigt werden.

Beschränkt man sich auf deklaratives SQL, so ist die Anpassung an die Werte nur halbautomatisch möglich. In einzelnen Spezialfällen kann man aber manchmal näherungsweise Lösungen mit deklarativem SQL  finden.

Mit »COUNT« und »GROUP BY« können einige Informationen in der Art „wie viele Entitäten haben bestimmte Eigenschaften“ erhalten werden. Manchmal reichen diese Informationen schon aus, und es ist nicht nötig eine vollständige pivot -Tabelle zu erzeugen.

Es wäre zwar mit »GROUP_CONCAT« vermutlich auch noch möglich, ein zweidimensionales „Kreuz-Format“ zu erhalten, aber dies wäre nicht unbedingt empfehlenswert, da die Daten dann mit den Mitteln von SQL  schwieriger weiterverarbeitet werden können.

DROP TABLE IF EXISTS p;
CREATE TABLE p( p INT NOT NULL, name VARCHAR( 32 ) NOT NULL, gender CHAR NOT NULL, dept VARCHAR( 16 ) NOT NULL );
SELECT * FROM p;
INSERT INTO p( p, name, gender, dept ) VALUES  
( 1, 'John',   'm', 'pers'  ),  
( 2, 'Mario',  'm', 'pers'  ),  
( 7, 'Mary',   'f', 'pers'  ), 
( 8, 'Bill',   'm', 'pers'  ), 
( 3, 'Frank',  'm', 'sales' ),  
( 5, 'Susan',  'f', 'sales' ),  
( 6, 'Martin', 'm', 'sales' ),  
( 4, 'Otto',   'm', 'dev'   ),  
( 9, 'June',   'f', 'dev'   );
SELECT * FROM p;

Werte (m, f) der Basistabelle werden Spaltenname der abgeleiteten Pivot-Tabelle. Dabei wird gezählt, wie oft diese Werte in jede Gruppe vorkommen.

Die Werte aus der Spalte »gender« sollen zu Spaltentiteln werden.

Die Werte aus der Spalte »dept« sollen zu Zeilentiteln werden.

Der Wert 1 soll dabei mit der Summe gefaltet (zusammengefaßt) werden, um die Kreuzwerte zu erhalten.

Die Kreuztabelle soll um eine Spalte mit der Summe der Kreuzwerte ergänzt werden.

SELECT 
  dept AS '',  
  COUNT( CASE WHEN gender = 'm' THEN p ELSE NULL END) AS m, 
  COUNT( CASE WHEN gender = 'f' THEN p ELSE NULL END) AS f 
FROM p 
GROUP BY dept;
+-------+---+---+ 
|       | m | f | 
+-------+---+---+ 
| dev   | 1 | 1 | 
| pers  | 3 | 1 | 
| sales | 2 | 1 | 
+-------+---+---+
SELECT 
  dept AS '',  
  COUNT( CASE WHEN gender = 'm' THEN p ELSE NULL END) AS m, 
  COUNT( CASE WHEN gender = 'f' THEN p ELSE NULL END) AS f, 
  COUNT( * )AS total 
FROM p 
GROUP BY dept;
+-------+---+---+-------+ 
|       | m | f | total | 
+-------+---+---+-------+ 
| dev   | 1 | 1 |     2 | 
| pers  | 3 | 1 |     4 | 
| sales | 2 | 1 |     3 | 
+-------+---+---+-------+ 
3 rows in set (0.12 sec)

Andere Produkte

In Microsoft ® Access  können Kreuztabellen mit »TRANSFORM« und »PIVOT« erstellt werden. Für PostgresQL  gibt es das Modul »tablefunc«, welches Kreuztabellen erzeugen kann. Für Perl  gibt es »DBIx::SQLCrosstab«.

Es ist auch möglich, Tabellen von MySQL in Microsoft ® Excel  zu importieren, und dann dort pivot -Tabellen erzeugen zu lassen.