HOWTO · MySQL

Pivot Tabelle in MySQL

Dieser Artikel zeigt verschiedene Möglichkeiten zum Generieren einer Pivot-Tabelle in MySQL.

Auf dieser Seite

Beim Pivotieren werden Zeilen in Spalten umgewandelt, häufig für eine Zusammenfassung oder einen Bericht. MySQL hat keine integrierte PIVOT-Funktion, aber mit bedingter Aggregation lassen sich feste Spalten mit IF oder CASE erstellen. Ändert sich die Menge der Ausgabespalten, kann dynamisches SQL die Abfrage anhand der Daten zusammensetzen. Das Beispiel verwendet Schülernoten: Durch die Gruppierung nach Schülername entsteht eine Zeile pro Name und eine Spalte pro Fach.

Beispieldaten vorbereiten

Die Beispieldaten enthalten zwei Noten für Sam im Fach 1. In der erwarteten Pivot-Tabelle wird die höhere Note, 99, verwendet. Für Martin gibt es keinen Eintrag für Fach 4; daher zeigen die Beispiele mit festen Spalten dort NULL. Die bereitgestellte Eingabevisualisierung zeigt dieselben Datensätze wie die folgenden Beispiele.

Eingabetabelle der Schüler mit den Spalten name, subjectid und marks

Für Sam gibt es zwei Einträge mit einer Note für Fach 1. Daher sollte die Pivot-Tabelle den höheren Wert 99 anzeigen.

Führen Sie dieses Beispiel aus:

Der Code und die angezeigten Bezeichnungen oder Ausgaben in diesem Abschnitt bleiben auf Englisch.

rows = [
    ("Sam", 1, 70), ("Sam", 2, 77), ("Sam", 3, 71), ("Sam", 4, 70),
    ("Sam", 1, 99), ("John", 1, 89), ("John", 2, 87),
    ("John", 3, 88), ("John", 4, 89), ("Martin", 1, 60),
    ("Martin", 2, 47), ("Martin", 3, 68),
]
subjects = (1, 2, 3, 4)
print("name|" + "|".join(f"Sub{s}" for s in subjects))
for name in sorted({row[0] for row in rows}):
    marks = [max((score for student, subject, score in rows
                  if student == name and subject == s), default="NULL")
             for s in subjects]
    print(name + "|" + "|".join(str(score) for score in marks))
name|Sub1|Sub2|Sub3|Sub4
John|89|87|88|89
Martin|60|47|68|NULL
Sam|99|77|71|70

Diese Python-Veranschaulichung macht die Gruppierung und die Auswahl der höchsten Note sichtbar. Verwenden Sie die folgenden MySQL-Abfragen für Ihre Tabelle. Das SQL zum Anlegen der Tabelle lautet CREATE TABLE student (name VARCHAR(20), subjectid INT, marks INT);. Fügen Sie die in der Abbildung gezeigten Zeilen hinzu, einschließlich Sams beider Einträge für Fach 1, und erstellen Sie den Bericht anschließend mit bedingter Aggregation.

Pivotieren mit IF und MAX

Schreiben Sie für jede Fachspalte einen bedingten Ausdruck und gruppieren Sie anschließend nach name, damit eine Zeile pro Schüler zurückgegeben wird: SELECT name, MAX(IF(subjectid=1, marks, NULL)) AS Sub1, MAX(IF(subjectid=2, marks, NULL)) AS Sub2, MAX(IF(subjectid=3, marks, NULL)) AS Sub3, MAX(IF(subjectid=4, marks, NULL)) AS Sub4 FROM student GROUP BY name;. IF gibt bei einer passenden Zeile die Note und andernfalls NULL zurück. MAX aggregiert diese Werte innerhalb jeder Namens- und Fachspalte. Passen mehrere Zeilen zu demselben Schüler und Fach, gibt MAX die höchste passende Note zurück; es wählt weder den ersten Eintrag aus noch begrenzt es die Anzahl der Zeilen.

Ergebnis der Pivot-Tabelle mit festen Spalten aus bedingter Aggregation

Das Ergebnis mit festen Spalten zeigt Sams höchste Note für Fach 1, 99, und NULL, wenn Martin kein Eintrag für Fach 4 hat.

Die Abbildung zeigt das Abfrageergebnis, darunter Sams Wert 99 für Fach 1 und NULL für Martins fehlenden Eintrag zu Fach 4. Es handelt sich um Ergebnisdarstellungen und nicht um Screenshots eines Datenbank-Clients. Das SQL und das Ergebnis bleiben hier auswählbar.

Pivotieren mit CASE

CASE formuliert dieselbe Bedingung ausdrücklich. Diese Abfrage liefert dasselbe Ergebnis mit festen Spalten wie die Variante mit IF: SELECT name, MAX(CASE WHEN subjectid=1 THEN marks END) AS Sub1, MAX(CASE WHEN subjectid=2 THEN marks END) AS Sub2, MAX(CASE WHEN subjectid=3 THEN marks END) AS Sub3, MAX(CASE WHEN subjectid=4 THEN marks END) AS Sub4 FROM student GROUP BY name;. Ohne ELSE ergibt eine nicht passende Zeile NULL; MAX ignoriert diese Werte. Gibt es für einen Schüler keine passende Zeile zu einem Fach, bleibt der Wert für dieses Fach daher NULL. Bei mehreren passenden Zeilen gilt dieselbe Regel: Es wird die höchste Note verwendet.

Dynamische Pivot-Spalten erzeugen

Die Abfragen mit IF und CASE haben feste Ausgabespalten: Beim Schreiben der Abfrage müssen die Fachwerte bekannt sein, und für jede Spalte muss ein Ausdruck ergänzt werden. Dynamisches SQL kann diese Ausdrücke anhand der unterschiedlichen Fach-IDs erzeugen. GROUP_CONCAT setzt die Spaltenausdrücke zusammen, und eine vorbereitete Anweisung führt die resultierende Abfrage aus:

SET SESSION group_concat_max_len = 100000; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) AS \’, subjectid, ‘`’) ORDER BY subjectid SEPARATOR ‘, ‘) INTO @columns FROM student; SET @sql = CONCAT(‘SELECT name, ‘, @columns, ’ FROM student GROUP BY name’); SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;`

Die generierte Abfrage enthält für jedes Fach, das in der Tabelle vorkommt, eine Ausgabespalte. In diesem Beispiel sind die Noten nicht negativ. Deshalb lässt ELSE 0 ein fehlendes Fach als 0 anzeigen, wie in der Ergebnisdarstellung zu sehen. Diese Wahl ist nur dann korrekt, wenn Noten nicht negativ sein können: Andernfalls könnte die 0 einer nicht passenden Zeile in MAX größer als eine passende negative Note sein. Können Noten negativ sein, verwenden Sie stattdessen ELSE NULL; dann ignoriert MAX die nicht passenden Zeilen, und für einen Schüler ohne passenden Eintrag wird der Wert für diese Spalte NULL. Prüfen Sie @sql vor dem Vorbereiten der Anweisung, besonders wenn Werte oder Bezeichner aus nicht vertrauenswürdigen Eingaben stammen können. Die generierte und die vorbereitete Anweisung sind an die Sitzung gebunden. Führen Sie diese Anweisungen daher in derselben MySQL-Verbindung aus. Für GROUP_CONCAT gilt eine konfigurierbare Maximallänge. Überschreitet die Liste der generierten Ausdrücke group_concat_max_len, kann sie abgeschnitten werden. Legen Sie einen geeigneten Sitzungswert fest und prüfen Sie das generierte SQL vor der Ausführung.

Dynamisches Pivot-Ergebnis mit Spalten für die einzelnen Fächer

Das generierte Ergebnis ergänzt eine Spalte für jede Fach-ID. Unter der Annahme, dass die Noten im Beispiel nicht negativ sind, wird Martins fehlender Eintrag für Fach 4 als 0 angezeigt.

Dieser Ansatz passt die Spaltenliste an die Fach-IDs in den Daten an. Die Ergebnisstruktur wird trotzdem beim Vorbereiten der Anweisung festgelegt. Bei sehr vielen oder häufig wechselnden Fächern kann es einfacher sein, die Daten mit Anwendungscode oder einem Berichtswerkzeug aufzubereiten.