So ändern Sie die Tabellenform, um Zeilen in Spalten in MySQL umzuwandeln

MySQL ist eines der beliebtesten relationalen Datenbankmanagementsysteme der Welt. MySQL ermöglicht es uns, Daten in tabellarischer Form zu speichern, zu verwalten und zu analysieren. Eine Tabelle besteht aus Zeilen und Spalten, die Entitäten und Datenattribute darstellen. Manchmal müssen wir jedoch die Form der Tabelle ändern, um Zeilen in Spalten oder umgekehrt umzuwandeln. Dies wird als Tabellentransposition bezeichnet.

Umsetzungstabellen können aus mehreren Gründen nützlich sein, z. B.:

• Vereinfachen Sie die Datenvisualisierung und das Datenverständnis
• Optimieren der Abfrage- und Indexleistung
• Passen Sie Datenstrukturen an Geschäfts- oder Anwendungsanforderungen an
• Ausführen von Aggregations- oder Berechnungsvorgängen für Daten

MySQL verfügt jedoch nicht über eine eingebaute Funktion, um Tabellen direkt zu transponieren. Daher müssen wir bestimmte Techniken oder Tricks anwenden, um dieses Ziel zu erreichen. In diesem Artikel besprechen wir verschiedene Möglichkeiten, die Form einer Tabelle zu ändern, um Zeilen in Spalten in MySQL umzuwandeln, zusammen mit Beispielen und Erklärungen.

Methode 1: Verwenden der Aggregationsfunktion und der GROUP BY-Klausel

Eine Möglichkeit, die Form einer Tabelle zu ändern, um Zeilen in Spalten in MySQL zu konvertieren, ist die Verwendung der Aggregationsfunktion und der GROUP BY-Klausel. Eine Aggregationsfunktion ist eine Funktion, die eine Reihe von Werten als Eingabe verwendet und einen einzelnen Wert als Ausgabe zurückgibt. Einige häufig verwendete Aggregationsfunktionen in MySQL sind:

• SUM(): Berechnet die Gesamtzahl eines Satzes von Werten
• AVG(): Berechnet den Durchschnitt eines Satzes von Werten
• MIN(): Gibt den kleinsten Wert einer Gruppe von Werten zurück
• MAX(): Gibt den größten Wert einer Gruppe von Werten zurück
• COUNT(): Zählt die Anzahl der Werte, die in einem Satz von Werten vorhanden sind

Eine GROUP BY-Klausel ist eine Klausel, die zum Gruppieren von Zeilen verwendet wird, die denselben Wert in einer oder mehreren Spalten aufweisen. Die GROUP BY-Klausel wird in der Regel in Verbindung mit einer Aggregationsfunktion verwendet, um Aggregatwerte für jede Gruppe zu berechnen.

Um das Tabellenformular so zu ändern, dass Zeilen mithilfe der Aggregationsfunktion und der GROUP BY-Klausel in Spalten konvertiert werden, müssen wir die folgenden Schritte ausführen:

• Geben Sie die Spalte an, die die neue Zeile in der Ergebnistabelle sein soll. Bei dieser Spalte handelt es sich in der Regel um eine Spalte, die unterschiedliche Werte aufweist und nicht zu viele. Beispiel: Jahr, Monat, Kategorie, Typ usw. Spalten.
• Geben Sie die Spalte an, die die neue Spalte in der Ergebnistabelle sein soll. Bei dieser Spalte handelt es sich in der Regel um eine Spalte, die für jede Zeile den gleichen oder einen begrenzten Wert aufweist. Zum Beispiel Name, Produkt, Marke usw. Spalten.
• Geben Sie die Aggregationsfunktion an, die zum Berechnen des Werts für jede neue Zeilen- und Spaltenkombination verwendet wird. Diese Aggregationsfunktion hängt von der Art der Daten und dem Zweck unserer Analyse ab. Beispiel: SUM(), AVG(), MIN(), MAX() usw.
• Erstellen Sie eine SQL-Abfrage, die die Aggregationsfunktion und die GROUP BY-Klausel verwendet, um die gewünschte Ergebnistabelle zurückzugeben.

Schauen wir uns ein Beispiel an, um dies besser zu verstehen. Angenommen, wir haben die folgende Tabelle, in der Produktverkaufsdaten für jeden Monat und jede Kategorie gespeichert sind:

Diese Tabelle enthält drei Spalten: Monat, Kategorie und Umsatz. Wir möchten die Form dieser Tabelle ändern, um Zeilen in Spalten umzuwandeln, damit wir den Gesamtumsatz für jede Kategorie und jeden Monat in einer Tabelle sehen können. Die gewünschte Ergebnistabelle sieht wie folgt aus:

Um dieses Ziel zu erreichen, können wir Weg 1 mit den folgenden Schritten verwenden:

• Geben Sie die Spalte an, die die neue Zeile in der Ergebnistabelle sein soll. In diesem Fall ist die Kategoriespalte eine neue Zeile, da sie unterschiedliche Werte und nicht zu viele (A, B, C) hat.
• Geben Sie die Spalte an, die die neue Spalte in der Ergebnistabelle sein soll. In diesem Fall ist die Monatsspalte die neue Spalte, da sie für jede Zeile den gleichen oder einen begrenzten Wert aufweist (Jan, Feb, März).
• Geben Sie die Aggregationsfunktion an, die zum Berechnen des Werts für jede neue Zeilen- und Spaltenkombination verwendet wird. In diesem Fall verwenden wir die Funktion SUM(), um den Gesamtumsatz für jede Kategorie und jeden Monat zu berechnen.
• Erstellen Sie eine SQL-Abfrage, die die Aggregationsfunktion und die GROUP BY-Klausel verwendet, um die gewünschte Ergebnistabelle zurückzugeben. Die SQL-Abfragen, die wir verwenden können, sind wie folgt:

SELECT
category
SUM(CASE WHEN month ='Jan' THEN sales ELSE 0 END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM
sales_table
GROUP BY
category;

Diese Abfrage führt die folgenden Schritte aus:

• Wählen Sie die Kategoriespalte aus der Tabelle sales_table
• Verwenden Sie die Funktionen SUM() und CASE, um den Gesamtumsatz für jede Kategorie und jeden Monat zu berechnen. CASE ist eine bedingte Anweisung, die einen Wert basierend auf einer bestimmten Bedingung zurückgibt. In dieser Abfrage verwenden wir CASE, um den monatlichen Wert zu überprüfen und gegebenenfalls den Verkaufswert oder 0 zurückzugeben, wenn dies nicht der Fall ist. Beispiel: SUM(CASE WHEN month =’Jan‘ THEN sales ELSE 0 END) gibt den Gesamtumsatz für Januar für jede Kategorie oder 0 zurück, wenn für diesen Monat keine Verkäufe vorhanden sind.
• Geben Sie jeder neuen Spalte mithilfe von AS einen Aliasnamen. Alias ist ein alternativer Name, den wir einer Spalte oder Tabelle geben können, um das Schreiben und Lesen einer Abfrage zu erleichtern. In dieser Abfrage geben wir jeder neuen Spalte einen Aliasnamen mit einem entsprechenden Monatsnamen, z. B. Jan, Feb und März.
• Verwenden Sie die GROUP BY-Klausel, um Zeilen nach der Kategoriespalte zu gruppieren. Die GROUP BY-Klausel erstellt eine Zeile für jeden eindeutigen Wert in der Kategoriespalte und berechnet einen Aggregatwert für jede zweite Spalte.

Diese Abfrage generiert die gewünschte Ergebnistabelle, wie oben gezeigt.

Weg 2: Verwenden der Pivot-Funktion

Eine weitere Möglichkeit, die Form einer Tabelle zu ändern, um Zeilen in Spalten in MySQL umzuwandeln, ist die Verwendung der pivot-Funktion. Die Pivot-Funktion ist eine Funktion, mit der wir die Form einer Tabelle ändern können, indem wir Daten von Zeilen in Spalten oder umgekehrt drehen. Mit der Pivot-Funktion können Tabellen einfach und schnell transponiert werden.

MySQL hat jedoch keine eingebaute Pivot-Funktion, die wir direkt verwenden können. Daher müssen wir bestimmte Erweiterungen oder Plugins verwenden, um Pivot-Funktionen zu MySQL hinzuzufügen. Eine der beliebtesten und am häufigsten verwendeten Erweiterungen ist Flexviews. Flexviews ist eine Erweiterung, die Pivot-, Unpivot- und inkrementelle Aktualisierungsfunktionen für MySQL bereitstellt. Flexviews kann von der offiziellen Website heruntergeladen und installiert werden: https://github.com/greenlion/swanhart-tools/tree/master/flexviews

Um die Form einer Tabelle zu ändern, um Zeilen mithilfe der Pivot-Funktion in Spalten umzuwandeln, müssen wir die folgenden Schritte ausführen:

• Installieren und aktivieren Sie die Flexviews-Erweiterung in MySQL. Befolgen Sie die Installationsanweisungen auf der offiziellen Website.
• Erstellen Sie eine temporäre Tabelle, die die Daten enthält, die wir formen möchten. Temporäre Tabellen sind Tabellen, die nur während einer MySQL-Sitzung existieren und nach Beendigung der Sitzung automatisch gelöscht werden. Temporäre Tabellen können mit dem Befehl CREATE TEMPORARY TABLE erstellt werden. Mit dem folgenden Befehl können wir beispielsweise eine temporäre Tabelle mit dem Namen sales_temp erstellen, die Daten aus der sales_table Tabelle enthält:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Erstellen Sie eine Ansicht, die die Definition der Pivotfunktion enthält, die wir verwenden möchten. Eine View ist ein Datenbankobjekt, das die Ergebnisse einer SQL-Abfrage als virtuelle Tabelle speichert. Ansichten können mit dem Befehl CREATE VIEW erstellt werden. Zum Beispiel können wir eine Ansicht mit dem Namen pivot_view erstellen, die die Definition der Pivot-Funktion enthält, um die Form der sales_temp Tabelle mit dem folgenden Befehl zu ändern:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'sales_temp', -- der Name der temporären Tabelle, die die Daten enthält
'category', -- der Name der Spalte, die die neue Zeile sein wird
'month', -- der Name der Spalte, die zur neuen Spalte wird
'sales', -- der Name der Spalte, die den zu berechnenden Wert enthält
'SUM', -- der Name der zu verwendenden Aggregationsfunktion
NULL -- zusätzliche Spaltennamen, die in die Ergebnistabelle aufgenommen werden sollen (optional)
) AS pivot_result; -- Aliasname für das Ergebnis der Pivot-Funktion

• Führen Sie eine SQL-Abfrage aus, um die Pivotfunktion der erstellten Ansicht aufzurufen. Die SQL-Abfragen, die wir verwenden können, sind wie folgt:

SELECT * FROM pivot_view;

Diese Abfrage generiert die gewünschte Ergebnistabelle, wie oben gezeigt.

Möglichkeit 3: Verwenden der Funktionen “Unpivot” und “Pivot”

Die dritte Möglichkeit, die Form einer Tabelle zu ändern, um Zeilen in Spalten in MySQL zu konvertieren, ist die Verwendung der Funktionen unpivot und pivot. Die Unpivot-Funktion ist die entgegengesetzte Funktion der Pivot-Funktion, die die Form der Tabelle ändert, indem Daten aus Spalten in Zeilen gedreht werden. Mit der Unpivot-Funktion können Tabellen flexibler und dynamischer transponiert werden.

MySQL hat jedoch auch keine eingebaute Unpivot-Funktion, die wir direkt verwenden können. Daher müssen wir auch bestimmte Erweiterungen oder Plugins verwenden, um MySQL eine Unpivot-Funktionalität hinzuzufügen. Eine der beliebtesten und häufig verwendeten Erweiterungen ist die Flexviews, die wir bereits besprochen haben. Flexviews bietet zusätzlich zu den Pivot- und inkrementellen Aktualisierungsfunktionen für MySQL eine Unpivot-Funktion.

Um die Tabellenform so zu ändern, dass Zeilen mithilfe der Funktionen unpivot und pivot in Spalten konvertiert werden, müssen wir die folgenden Schritte ausführen:

• Installieren und aktivieren Sie die Flexviews-Erweiterung in MySQL, falls dies noch nicht geschehen ist. Befolgen Sie die Installationsanweisungen auf der offiziellen Website
• Erstellen Sie eine temporäre Tabelle, die die Daten enthält, die wir formen möchten, falls sie noch nicht vorhanden waren. Temporäre Tabellen können mit dem Befehl CREATE TEMPORARY TABLE erstellt werden. Mit dem folgenden Befehl können wir beispielsweise eine temporäre Tabelle mit dem Namen sales_temp erstellen, die Daten aus der sales_table Tabelle enthält:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Erstellen Sie eine Ansicht, die die Definition der Unpivot-Funktion enthält, die wir verwenden möchten. Eine View ist ein Datenbankobjekt, das die Ergebnisse einer SQL-Abfrage als virtuelle Tabelle speichert. Ansichten können mit dem Befehl CREATE VIEW erstellt werden. Zum Beispiel können wir eine Ansicht mit dem Namen unpivot_view erstellen, die eine Unpivot-Funktionsdefinition enthält, um die Form einer sales_temp Tabelle mit dem folgenden Befehl zu ändern:

CREATE VIEW unpivot_view AS
SELECT
FV$Unpivot(
'sales_temp', -- der Name der temporären Tabelle, die die Daten enthält
'category', -- der Name der Spalte, die in der Zeile verbleibt
'month', -- der Name der neuen Spalte, in der der alte Spaltenname gespeichert wird
'sales', -- der Name der neuen Spalte, in der die alten Spaltenwerte gespeichert werden
'month, sales' -- der Name der alten Spalte, die in eine Zeile umgewandelt werden soll
) AS unpivot_result; -- Aliasname für die Ergebnisse der Unpivot-Funktion

• Erstellen Sie eine weitere Ansicht, die die Definition der Pivotfunktion enthält, die wir verwenden möchten. Ansichten können mit dem Befehl CREATE VIEW erstellt werden. Zum Beispiel können wir eine Ansicht mit dem Namen pivot_view erstellen, der die Definition der Pivot-Funktion enthält, um die Form der unpivot_view Tabelle mit dem folgenden Befehl zu ändern:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'unpivot_view', -- der Anzeigename, der die Daten enthält
'category', -- der Name der Spalte, die die neue Zeile sein wird
'month', -- der Name der Spalte, die zur neuen Spalte wird
'sales', -- der Name der Spalte, die den zu berechnenden Wert enthält
'SUM', -- der Name der zu verwendenden Aggregationsfunktion
NULL -- zusätzliche Spaltennamen, die in die Ergebnistabelle aufgenommen werden sollen (optional)
) AS pivot_result; -- Aliasname für das Ergebnis der Pivot-Funktion

• Führen Sie eine SQL-Abfrage aus, um die Pivotfunktion der erstellten Ansicht aufzurufen. Die SQL-Abfragen, die wir verwenden können, sind wie folgt:

SELECT * FROM pivot_view;

Diese Abfrage generiert die gewünschte Ergebnistabelle, wie oben gezeigt.

Schlussfolgerung

In diesem Artikel haben wir verschiedene Möglichkeiten besprochen, wie Sie die Form einer Tabelle ändern können, um Zeilen in Spalten in MySQL zu konvertieren, zusammen mit Beispielen und Erklärungen. Einige der Möglichkeiten, die wir nutzen können, sind:

• Verwenden von Aggregationsfunktionen und GROUP BY-Klauseln
• Verwenden der Pivot-Funktion
• Verwenden Sie die Funktionen “Unpivot” und “Pivot”

Jede Methode hat ihre Vor- und Nachteile, abhängig von der Datenstruktur, dem Zweck der Analyse und unseren Vorlieben. Wir können den Weg wählen, der unseren Bedürfnissen und Bedingungen am besten entspricht.

Wir hoffen, dass dieser Artikel nützlich ist und Ihnen beim Transponieren von Tabellen in MySQL helfen kann. Vielen Dank, dass Sie diesen Artikel bis zum Ende gelesen haben.