Pivot Tabelle in MySQL

1. Erstellen eine Pivot-Tabelle in MySQL mit der Funktion MAX und der Anweisung IF
2. Erstellen Sie eine Pivot-Tabelle in MySQL mit einer CASE-Anweisung
3. Erstellen eine Pivot-Tabelle in MySQL mit dynamischen Pivot-Spalten

In diesem Artikel beschreiben wir, wie Tabellendaten von Zeilen in Spalten umgewandelt werden. Dieser Vorgang wird als Pivotieren bezeichnet. Das Ergebnis dieser Transformation ist normalerweise eine zusammenfassende Tabelle, in der wir Informationen präsentieren, die für die Berichterstellung geeignet sind.

In MySQL gibt es keine eingebaute Funktion zum Erstellen von Pivot-Tabellen, daher müssen Sie eine MySQL-Abfrage schreiben, um eine Pivot-Tabelle zu generieren. Glücklicherweise gibt es drei verschiedene Möglichkeiten, eine Pivot-Tabelle mit MySQL zu erstellen.

1. Erstellen Sie eine Pivot-Tabelle in MySQL mit der IF-Anweisung
2. Erstellen Sie eine Pivot-Tabelle in MySQL mit einer CASE-Anweisung
3. Erstellen Sie eine Pivot-Tabelle in MySQL mit dynamischen Pivot-Spalten

Das folgende Skript erstellt eine Tabelle student mit drei Spalten (name, subjectid und marks).

create table student(name varchar(20),subjectid int(10), marks int(10));

Hier Einfügen wir Beispieldatenwerte in die Schülertabelle für die Demonstration.

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

Nach dem Einfügen dieser Werte schreiben wir eine select-Abfrage, um Datensätze in der Datenbank anzuzeigen.

Select * from student;

Die Ausgabe wird sein:

Erstellen eine Pivot-Tabelle in MySQL mit der Funktion MAX und der Anweisung IF

Dies ist eine sehr einfache Herangehensweise an das Problem. Bei diesem Ansatz verwenden wir die Funktion MAX und die Anweisung IF. Liegen für einen Studierenden mehrere Einträge zu einem Fach in der Datenbank vor, werden maximal zwei Punkte vergeben.

Lassen Sie uns nun sehen, wie Sie dies mit der Abfrage SELECT verwenden:

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;

Die Ausgabe wird sein:

Diese Ausgabe ist ein zusammengestelltes Ergebnis für jeden Schüler fachbezogen. Da Sie für jeden Schüler eine Zeile benötigen, müssen Sie nach der Namensspalte gruppieren. Außerdem müssen Sie für jede Spalte eine Bedingung angeben, d. h. eine Bedingung pro Betreff.

Erstellen Sie eine Pivot-Tabelle in MySQL mit einer CASE-Anweisung

Dies ist auch eine einfache Herangehensweise an das Problem. In diesem Ansatz verwenden wir die Funktion MAX zusammen mit der Anweisung CASE. Auch wenn für einen Studierenden mehrere Einträge zu einem Fach in der Datenbank vorhanden sind, werden maximal zwei Punkte vergeben. Wenn für einige Schüler keine Noten vorhanden sind, wird der Wert NULL angenommen.

Sehen wir uns nun an, wie Sie dies mit der Abfrage SELECT verwenden:

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

Die Ausgabe wird sein:

Erstellen eine Pivot-Tabelle in MySQL mit dynamischen Pivot-Spalten

Eine Aggregatfunktion (MAX), IF-Anweisung und CASE-Anweisung haben die Pivot-Tabelle im obigen Beispiel generiert. Der Nachteil dieses Ansatzes besteht darin, dass wir beim Schreiben der Abfrage die Spaltenüberschriften kennen müssen, und wenn die Anzahl der Spalten steigt, ändert sich auch der Code. Für kleinere Ergebnisse und alle möglichen Werte können wir beide oben genannten Ansätze verwenden.

Um diese Einschränkungen zu überwinden, können wir dynamische Pivot-Spalten verwenden. Hier kann die Funktion GROUP_CONCAT die Spalten einer PIVOT-Tabellenausgabe dynamisch generieren.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, 
 ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In einer Funktion GROUP_CONCAT verwenden wir das Schlüsselwort DISTINCT, um eine eindeutige Liste von Markierungen zu erhalten. Andernfalls würde unsere Abfrage einen Fehler ausgeben, da die Liste zu lang ist. Sie können Select * from @sql schreiben, um die dynamische Abfrage jederzeit zu überprüfen.

Wenn wir nun diese Abfrage ausführen, erhalten wir folgendes Ergebnis:

Beachten Sie, dass die Spaltenüberschriften dynamisch basierend auf den Werten in der Tabelle generiert werden und dass die Spaltenüberschrift die Betreff-ID darstellt.