PIVOT in SQL: Zeilen in Spalten verwandeln

Angenommen, in der Datenbank liegen Produktverkäufe nach Quartal.
Ein Standard-GROUP BY liefert dieses Ergebnis:

Für den Report willst du es aber so haben, mit Quartalen als Spalten:

So eine Umwandlung von Zeilen in Spalten heißt PIVOT. Im Artikel schauen wir, wie sich das mit reinen SQL-Mitteln umsetzen lässt, am Beispiel einer Lieferdienst-Datenbank.

Das Problem: „langes" Format

In dieser Datenbank gibt es die Tabelle events mit Nutzeraktionen auf verschiedenen Plattformen. Zählen wir die Ereignisse nach Typ und Plattform:

MySQL 8.1
SELECT
    event_name,
    platform,
    COUNT(*) AS cnt
FROM events
GROUP BY event_name, platform
ORDER BY event_name, platform;

Das ist das sogenannte „lange" Format. Jede Kombination „Ereignis + Plattform" belegt eine eigene Zeile. Um iOS und Android für ein Ereignis zu vergleichen, muss man die passenden Zeilen mit dem Auge suchen.

Wir wollen die Daten aber so sehen:

Die Plattformen sind Spalten geworden, und jeder Ereignistyp belegt eine Zeile. Genau dieses Ergebnis wollen wir.

Wie man PIVOT baut: Algorithmus

Bevor du den Query schreibst, beantworte vier Fragen:

1. Was werden die Zeilen? event_name (Ereignistyp)
2. Was werden die Spalten? platform (iOS, Android, Web)
3. Was steht als Wert in den Zellen? COUNT(*) (Anzahl der Ereignisse)

CASE + GROUP BY: der universelle Weg

Dieser Ansatz funktioniert in jedem DBMS: MySQL, PostgreSQL, SQLite, SQL Server.

MySQL 8.1
SELECT
    event_name,
    SUM(CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END) AS iOS,
    SUM(CASE WHEN platform = 'Android' THEN 1 ELSE 0 END) AS Android,
    SUM(CASE WHEN platform = 'Web' THEN 1 ELSE 0 END) AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

Aber wie funktioniert das? Gehen wir Schritt für Schritt durch.

Was im Inneren passiert

Um die Mechanik zu verstehen, lassen wir SUM und GROUP BY weg und schauen, was CASE für jede einzelne Zeile ausrechnet:

MySQL 8.1
SELECT
    event_name,
    platform,
    CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END AS "iOS",
    CASE WHEN platform = 'Android' THEN 1 ELSE 0 END AS "Android",
    CASE WHEN platform = 'Web' THEN 1 ELSE 0 END AS "Web"
FROM events
WHERE event_name IN ('app_open', 'purchase')
LIMIT 6;

Jedes CASE prüft die Plattform und setzt 1 bei Treffer, sonst 0. Hier ein Auszug aus dem Ergebnis zur Veranschaulichung:

Hier siehst du einen Teil der Zeilen für zwei Gruppen: app_open und purchase. Jede Zeile bekommt genau in einer Spalte eine 1, in den anderen 0. Eine Zeile mit platform = 'iOS' zum Beispiel bekommt die 1 nur in iOS.

Dann gruppiert GROUP BY die Zeilen nach event_name, und SUM addiert die Werte in jeder Gruppe. So viele Einsen in der Spalte iOS zusammenkommen, so viele Ereignisse kamen von iOS.

Achte auf das ELSE 0 im CASE: ohne es bekommen die Zeilen ohne Treffer NULL statt 0, und das Ergebnis von SUM kann am Ende ebenfalls NULL sein. Mehr dazu im Abschnitt „NULL-Falle" weiter unten.

Warum SUM und nicht COUNT? SUM ist praktischer: Du tauschst einfach die 1 gegen amount aus und zählst statt Anzahl die Summen. Genau das machen wir im nächsten Beispiel.

MySQL 8.1
SELECT
    event_name,
    COUNT(*) FILTER (WHERE platform = 'iOS') AS iOS,
    COUNT(*) FILTER (WHERE platform = 'Android') AS Android,
    COUNT(*) FILTER (WHERE platform = 'Web') AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

Von Anzahlen zu Summen

Bisher haben wir die Anzahl der Ereignisse gezählt. Was, wenn wir Geldsummen brauchen? Ersetze einfach die 1 durch eine echte Spalte, etwa total_amount. Nehmen wir eine andere Tabelle aus derselben Datenbank, orders, und bauen einen Report mit Umsatz nach Bestellstatus für jeden Monat:

MySQL 8.1
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered,
    SUM(CASE WHEN status = 'canceled' THEN total_amount ELSE 0 END) AS canceled,
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

MySQL 8.1
SELECT
    to_char(order_date, 'YYYY-MM') AS month,
    SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered,
    SUM(total_amount) FILTER (WHERE status = 'canceled') AS canceled,
    SUM(total_amount) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

Worauf du achten solltest

Die NULL-Falle

In den Beispielen oben haben wir ELSE 0 im CASE verwendet, um ein numerisches Ergebnis zu garantieren. Schreibst du CASE aber ohne ELSE:

MySQL 8.1
SUM(CASE WHEN status = 'refunded' THEN total_amount END)

…und gibt es in einem Monat keine einzige Rückerstattung, gibt CASE für alle Zeilen NULL zurück, und SUM aus lauter NULL liefert NULL. Das ist kritisch, wenn anschließend gerechnet wird: 100 - NULL = NULL.

Vermeiden lässt sich das auf zwei Wegen:

1. ELSE 0 direkt im CASE ergänzen (wie in den Beispielen oben):

   MySQL 8.1
   SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END)
   

2. In COALESCE einpacken. Nützlich, wenn in der Spalte ohnehin NULL vorkommen können:

   MySQL 8.1
   COALESCE(SUM(CASE WHEN status = 'refunded' THEN total_amount END), 0)
   

Die Spaltenwerte muss man vorher kennen

Alle Ansätze verlangen, dass du die Werte, die zu Spalten werden ('iOS', 'Android', 'Web'), explizit aufzählst. Taucht in den Daten eine neue Plattform auf, musst du den Query manuell aktualisieren.

Der einzige Ausweg: ein Skript auf der Applikationsseite (Python, PHP, JS) oder dynamisches SQL (Query-String über PREPARE / Stored Procedures zusammenbauen). Mit reinem, statischem SQL lässt sich das nicht lösen.

In den meisten echten Aufgaben ist die Menge der Kategorien bekannt und ändert sich selten, daher ist Hardcoding völlig in Ordnung.

MySQL 8.1
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$
    SELECT event_name, platform, COUNT(*)
    FROM events
    GROUP BY event_name, platform
    ORDER BY event_name, platform
    $$,
    $$ VALUES ('Android'), ('iOS'), ('Web') $$
) AS ct(event_name TEXT, "Android" BIGINT, "iOS" BIGINT, "Web" BIGINT);

Zusammengefasst