UNPIVOT & PIVOT

Datenanalyse ist mehr als nur das Sammeln von Informationen. Oft liegt der Schlüssel zum Erfolg darin, wie du diese Daten präsentierst und analysierst. In SQL bieten dir die Befehle PIVOT und UNPIVOT mächtige Werkzeuge, um Daten effizient zu transformieren. Dieser Artikel führt dich Schritt für Schritt durch die Anwendung dieser Befehle und zeigt dir, wie du sie optimal einsetzt.

Was sind PIVOT und UNPIVOT?

Der PIVOT-Befehl wandelt Daten aus Zeilen in Spalten um. Stell dir vor, du hast eine Tabelle, in der Verkaufszahlen pro Monat untereinander aufgelistet sind. Mit PIVOT kannst du diese Monate als Spalten darstellen und erhältst so eine übersichtlichere Ansicht.

UNPIVOT ist das Gegenstück zu PIVOT. Es ermöglicht dir, Daten aus Spalten in Zeilen umzuwandeln. Wenn du beispielsweise eine Tabelle mit mehreren Monats-Spalten hast, kannst du diese in einzelne Zeilen transformieren, um Analysen zu erleichtern.

Wann und warum solltest du sie einsetzen?

• Datenbereinigung und -vorbereitung: Oft erhältst du Daten nicht in der gewünschten Struktur. Mit PIVOT und UNPIVOT bringst du sie in die Form, die du für Analysen oder Berichte benötigst.
• Verbesserte Datenvisualisierung: Durch die Umwandlung kannst du Daten besser visualisieren, beispielsweise in BI-Tools wie Power BI oder Tableau.
• Flexiblere Abfragen: Komplexe Datenstrukturen lassen sich einfacher abfragen, wenn sie passend transformiert wurden.

SQL: Handbuch für Einsteiger: Der leichte Weg zum SQL-Experten

17,99 €

Jetzt auf Amazon kaufen

• Marke: BMU Media GmbH
• SQL: Handbuch für Einsteiger: Der leichte Weg zum SQL-Experten

Ein praktisches Beispiel

Nehmen wir an, wir haben eine Tabelle Verkaufsdaten, die den Umsatz eines Geschäfts für verschiedene Monate darstellt:

SELECT Produkt, [Januar], [Februar], [März]
FROM
(
SELECT Produkt, Monat, Umsatz
FROM Verkaufsdaten
) AS Quelle
PIVOT
(
SUM(Umsatz)
FOR Monat IN ([Januar], [Februar], [März])
) AS PivotTabelle;

Ergebnis:

Anwendung von UNPIVOT

Hast du hingegen folgende Tabelle Monatsumsätze:

Und möchtest die Daten in Zeilenform bringen:

SELECT Produkt, Monat, Umsatz
FROM
(
SELECT Produkt, Januar, Februar, März
FROM Monatsumsätze
) AS Quelle
UNPIVOT
(
Umsatz FOR Monat IN (Januar, Februar, März)
) AS UnpivotTabelle;

Ergebnis:

Mit UNPIVOT haben wir die monatlichen Umsatzspalten in einzelne Zeilen umgewandelt, was eine klarere Sicht auf die monatlichen Verkaufstrends für jedes Produkt ermöglicht.

Der UNPIVOT-Befehl ist ein mächtiges Werkzeug in Ihrem SQL-Arsenal. Ob Sie Daten für die Analyse vorbereiten, bessere Visualisierungen erstellen oder einfach flexiblere Abfragen gestalten möchten – mit UNPIVOT können Sie Ihre Daten in neue Perspektiven transformieren. Tauchen Sie ein und experimentieren Sie damit!

Geht UNPIVOT auch auf anderen relationalen Datenbanken wie Oracle?

Ja, das Konzept des „Unpivoting“ von Daten, also das Umwandeln von Spalten in Zeilen, ist in vielen relationalen Datenbankverwaltungssystemen (DBMS) vorhanden, wenn auch manchmal unter verschiedenen Syntaxen oder Mechanismen. Lassen Sie uns dies an weiteren vier DBMS durchgehen:

1. MSSQL (Microsoft SQL Server):
   • Unterstützt direkt UNPIVOT.
   • Das Gegenstück dazu ist PIVOT, das Zeilen in Spalten umwandelt.
2. Oracle:
   • Oracle hat keine eingebaute UNPIVOT-Funktion bis zur Version 10g. Ab Oracle 11g wurde jedoch die UNPIVOT-Syntax eingeführt, die ähnlich wie bei MSSQL funktioniert.
   • Das Gegenstück in Oracle ist ebenfalls PIVOT.
3. IBM DB2:
   • IBM DB2 hat keine direkte UNPIVOT-Funktion. Das Unpivoting muss hier mit einer Kombination aus SQL-Funktionen erreicht werden, beispielsweise mit Hilfe von UNION ALL. Es ist etwas umständlicher als in MSSQL oder Oracle.
   • Ebenso gibt es keine direkte PIVOT-Funktion in DB2. Das Pivoting erfordert eine spezifische SQL-Abfragestruktur, oft unter Verwendung von Aggregatfunktionen.
4. Teradata:
   • Teradata unterstützt ebenfalls kein eingebautes UNPIVOT. Ähnlich wie bei DB2 müssen Sie hier Standard-SQL-Funktionen und -Techniken verwenden, um das gewünschte Ergebnis zu erzielen.
   • Das Gegenstück, das Pivoting, erfordert ebenfalls spezielle SQL-Abfragen.

Während UNPIVOT (und sein Gegenstück PIVOT) in einigen Systemen eingebaut ist und eine recht direkte Syntax bietet, erfordern andere Systeme einen kreativeren Ansatz mit Standard-SQL-Techniken. Es ist wichtig, sich mit den spezifischen Möglichkeiten und Einschränkungen des jeweiligen DBMS vertraut zu machen, wenn man solche Transformationen durchführt.

Datenbankentwicklung lernen mit SQL Server 2022: Der praxisorientierte Grundkurs – auch für SQL Server Express

39,90 €

Jetzt auf Amazon kaufen

PIVOT Beispiele

PIVOT ist das genaue Gegenteil von UNPIVOT. Während UNPIVOT Spalten in Zeilen umwandelt, wandelt PIVOT Zeilen in Spalten um.

Lassen Sie uns das am Beispiel von SQL Server durchgehen, da er eine direkte Unterstützung für PIVOT bietet:

Ausgangssituation:

Angenommen, wir haben folgende Tabelle mit monatlichen Verkaufsdaten für verschiedene Produkte:

| Product | Month   | Sales |
|---------|---------|-------|
| Apples  | January | 100   |
| Apples  | February| 150   |
| Apples  | March   | 130   |
| Bananas | January | 80    |
| Bananas | February| 85    |
| Bananas | March   | 90    |

Nun möchten wir die Monate als Spalten und die Produkte als Zeilen darstellen:

Beispiel:

SELECT Product, [January], [February], [March]
FROM
(SELECT Product, Month, Sales
FROM SalesTable) s
PIVOT
(SUM(Sales)
FOR Month IN ([January], [February], [March])
) AS pvt;

Ergebnis:

| Product | January | February | March |
|---------|---------|----------|-------|
| Apples  | 100     | 150      | 130   |
| Bananas | 80      | 85       | 90    |

Die PIVOT-Operation hat die monatlichen Verkaufszahlen pro Produkt in einer zeilenbasierten Ansicht in eine spaltenbasierte Ansicht umgewandelt.

Für Oracle sieht die Syntax ähnlich aus, obwohl es Unterschiede in der genauen Implementierung gibt. Bei IBM DB2 und Teradata, wie bereits erwähnt, gibt es keine direkte PIVOT-Syntax, aber Sie können mit Aggregatfunktionen und bedingten Anweisungen (z.B. CASE) ähnliche Ergebnisse erzielen. Das erfordert mehr Code und möglicherweise auch mehr Überlegungen im Hinblick auf die Datenstruktur und den gewünschten Output.

Vergleich mit Alternativen

Verwendung von temporären Tabellen

Anstatt PIVOT direkt zu nutzen, kannst du Zwischenergebnisse in temporären Tabellen speichern:

SELECT Produkt, Monat, Umsatz
INTO #TempTabelle
FROM Verkaufsdaten
WHERE Monat IN ('Januar', 'Februar', 'März');

-- Weiterverarbeitung der #TempTabelle

Einsatz von CASE-Ausdrücken

Mit CASE kannst du Daten manuell transponieren:

SELECT
  Produkt,
  SUM(CASE WHEN Monat = 'Januar' THEN Umsatz ELSE 0 END) AS Januar,
  SUM(CASE WHEN Monat = 'Februar' THEN Umsatz ELSE 0 END) AS Februar,
  SUM(CASE WHEN Monat = 'März' THEN Umsatz ELSE 0 END) AS März
FROM Verkaufsdaten
GROUP BY Produkt;

Weitere Beispiele

Angenommen, du hast eine Tabelle Verkaufsdaten, die Verkaufszahlen pro Produkt, Monat und Region enthält. Allerdings sind die Regionen als Spalten dargestellt, und du möchtest eine Analyse erstellen, bei der die Monate als Spalten und die Regionen als Zeilen dargestellt werden.

Ausgangstabelle Verkaufsdaten:

Ziel

• Schritt 1: Die Regionsspalten (Region_Nord, Region_Süd) sollen in Zeilen umgewandelt werden (UNPIVOT).
• Schritt 2: Die Monatswerte sollen von Zeilen in Spalten umgewandelt werden (PIVOT), sodass die Umsätze pro Region und Monat dargestellt werden.

Schritt-für-Schritt-Anleitung

1. UNPIVOT anwenden, um die Regionen in Zeilen umzuwandeln

SELECT
  Produkt,
  Monat,
  Region,
  Umsatz
FROM
  Verkaufsdaten
UNPIVOT
(
  Umsatz FOR Region IN (Region_Nord, Region_Süd)
) AS UnpivotTabelle;

Ergebnis nach UNPIVOT:

2. PIVOT anwenden, um die Monate in Spalten umzuwandeln

Nun verwenden wir das Ergebnis des UNPIVOT-Schritts als Grundlage für den PIVOT-Schritt.

SELECT
  Produkt,
  Region,
  [Januar],
  [Februar],
  [März]
FROM
(
  SELECT
    Produkt,
    Monat,
    Region,
    Umsatz
  FROM
    Verkaufsdaten
  UNPIVOT
  (
    Umsatz FOR Region IN (Region_Nord, Region_Süd)
  ) AS UnpivotTabelle
) AS UnpivotErgebnis
PIVOT
(
  SUM(Umsatz) FOR Monat IN ([Januar], [Februar], [März])
) AS PivotTabelle
ORDER BY
  Produkt,
  Region;

Ergebnis nach PIVOT:

Erklärung

• UNPIVOT-Schritt:
  • Wir haben die Spalten Region_Nord und Region_Süd in Zeilen umgewandelt.
  • Dies ermöglicht es uns, die Regionen dynamisch zu verarbeiten und später zu pivotieren.
• PIVOT-Schritt:
  • Wir haben die Monate Januar, Februar und März als Spalten dargestellt.
  • Die Umsätze wurden summiert, was in diesem Fall die einzelnen Werte darstellt (da es keine Duplikate gibt).

Zusammenfassung

Durch die Kombination von UNPIVOT und PIVOT haben wir die ursprüngliche Tabelle erfolgreich transformiert:

• Von: Produkt- und Monatsdaten mit Regionen als Spalten.
• Zu: Produkt- und Regionsdaten mit Monaten als Spalten.

Diese Technik ist besonders nützlich, wenn du komplexe Datensätze hast, die mehrere Pivotierungen erfordern, um die gewünschte Datenstruktur für Analysen oder Berichte zu erhalten.

Weitere Anmerkungen

• Flexibilität: Die Kombination von PIVOT und UNPIVOT bietet große Flexibilität bei der Datenumstrukturierung.
• Performance: Bei großen Datenmengen solltest du die Performance im Auge behalten und gegebenenfalls Indizes oder temporäre Tabellen verwenden.
• Dynamisches SQL: Wenn die Anzahl der Regionen oder Monate variabel ist, kann es sinnvoll sein, dynamisches SQL zu verwenden.

Praktisches Beispiel mit dynamischem SQL (optional)

Wenn die Anzahl der Monate oder Regionen nicht bekannt ist, kannst du dynamisches SQL einsetzen, um die Abfrage zur Laufzeit zu generieren.

Beispiel für dynamisches PIVOT:

DECLARE @DynamischePivotAbfrage NVARCHAR(MAX);
DECLARE @MonatsListe NVARCHAR(MAX);

-- Liste der Monate dynamisch erzeugen
SELECT @MonatsListe = STRING_AGG(QUOTENAME(Monat), ',') FROM (SELECT DISTINCT Monat FROM Verkaufsdaten) AS Monate;

SET @DynamischePivotAbfrage = N'
SELECT
  Produkt,
  Region,
  ' + @MonatsListe + '
FROM
(
  SELECT
    Produkt,
    Monat,
    Region,
    Umsatz
  FROM
    Verkaufsdaten
  UNPIVOT
  (
    Umsatz FOR Region IN (Region_Nord, Region_Süd)
  ) AS UnpivotTabelle
) AS UnpivotErgebnis
PIVOT
(
  SUM(Umsatz) FOR Monat IN (' + @MonatsListe + ')
) AS PivotTabelle
ORDER BY
  Produkt,
  Region;
';

EXEC sp_executesql @DynamischePivotAbfrage;

Hinweis: Das obige Beispiel verwendet die Funktion STRING_AGG, die in neueren SQL Server-Versionen verfügbar ist. Für ältere Versionen kannst du alternative Methoden zur String-Konkatenation verwenden.

Die Kombination von PIVOT und UNPIVOT ermöglicht es dir, komplexe Datentransformationen in SQL durchzuführen. Indem du zuerst die Daten entfaltest (UNPIVOT) und dann neu strukturierst (PIVOT), kannst du nahezu jede gewünschte Tabellenform erreichen.

Jetzt bist du dran: Experimentiere mit deinen eigenen Datensätzen und probiere aus, wie du PIVOT und UNPIVOT kombinieren kannst, um deine Datenanalyse zu optimieren.

Häufig gestellte Fragen zu PIVOT und UNPIVOT in SQL

Ähnliche Beiträge:

Mögliche Zwei-Faktor-Authentifizierung (2FA) beim Microsoft SQL Server 2022 Erweiterung der Rendering-Methoden in SSRS Medianwert mit einer Prozedur berechnen SQL Server und JSON: Laden, Analysieren und Verstehen von JSON-Daten Mögliche Dokumentation für Objekte im Report anzeigen Ermitteln von Abhängigkeiten zwischen Berichtselementen und Datenquellen